Re: Globalization question
- From: "David Pintor" <painterman@xxxxxxxxx>
- To: "Nuno Souto" <dbvision@xxxxxxxxxxxx>
- Date: Thu, 11 Dec 2008 11:46:59 +0000
Hi Nuno,
By the 'statement will fail' I understand that the statement 'ALTER DATABASE
SET TIME_ZONE...' will generate an error, as there is a table in the
database with a 'TIMESTAMP TO LOCAL TIME ZONE' column (ORDERS), but it
doesn't.
The date in NY does not change by altering the Time Zone:
SQL> ALTER DATABASE SET TIME_ZONE='Europe/London';
Database altered.
SQL> select * from orders;
ORDER_DATE
---------------------------------------------------------------------------
18-AUG-00 10.26.44 PM AMERICA/NEW_YORK
23-AUG-02 12.46.34 PM AMERICA/NEW_YORK
SQL> ALTER DATABASE SET TIME_ZONE='AMERICA/NEW_YORK';
Database altered.
SQL> select * from orders;
ORDER_DATE
---------------------------------------------------------------------------
18-AUG-00 10.26.44 PM AMERICA/NEW_YORK
23-AUG-02 12.46.34 PM AMERICA/NEW_YORK
Thanks and regards,
David
2008/12/11 Nuno Souto <dbvision@xxxxxxxxxxxx>
> I don't understand the "statement will fail" bit.
> But your select sysdate from dual should not change:
> 10:00 is 10:00, no matter what time zone you're in.
> It's the time in other timezones than yours that changes.
>
> If your timezone is Europe/London and your system
> says it's 10:00, then it's 10:00 in Europe/London, period.
> If you then change the database timezone to America/New_York,
> it'll still be 10:00 according to the system, but now in NY.
>
> What happens if you set database to Europe/London and
> you query with timezone of America/New_York, then
> you change the timezone to the latter and you query
> AGAIN for NY?
>
> --
> Cheers
> Nuno Souto
> in rainy Sydney, Australia
> dbvision@xxxxxxxxxxxx
>
> David Pintor wrote,on my timestamp of 11/12/2008 9:54 PM:
>
> /Answer:/
>> /The statement will fail./
>>
>>
>> When I run this on my database, the ALTER DATABASE... statement does not
>> fail (see below). However, I have tried putting London, Athens or whatever
>> city and when I query sysdate from dual I always get my local time. So I
>> guess the last one is the correct one? or did I miss anything here?
>>
>> *SQL> CREATE TABLE ORDERS (ORDER_DATE TIMESTAMP(0) WITH TIME ZONE);
>>
>> Table created.
>>
>> SQL> INSERT INTO ORDERS VALUES('18-AUG-00 10:26:44 PM America/New_York');
>>
>> 1 row created.
>>
>> SQL> INSERT INTO ORDERS VALUES('23-AUG-02 12:46:34 PM America/New_York');
>>
>> 1 row created.
>>
>> SQL> commit;
>>
>> Commit complete.
>>
>> SQL> ALTER DATABASE SET TIME_ZONE='Europe/London';
>>
>> Database altered.*
>>
>>
>>
>
>
>
Other related posts:
