Hello,
I am trying to understand how Daylight Savings Time is handled in Oracle Database.
I have read through the "Oracle Database Globalization Support Guide", in particular
this part onwards to understand how it works.
However, I have some doubts about the way things work.
I am particularly interested in how the ambiguous date-times are handled.
I have been trying to find the reason as to why same query returns different values when executed through SQL Developer and from SQL*Plus
Following are the details:
In SQL*Plus session
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select sessiontimezone, dbtimezone from dual ;
SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
+01:00
+00:00
SQL> select to_timestamp('31-MAR-2013 01:00:00','DD-MON-YYYY HH24:MI:SS') from dual ;
TO_TIMESTAMP('31-MAR-201301:00:00','DD-MON-YYYYHH24:MI:SS')
---------------------------------------------------------------------------
31-MAR-13 01.00.00.000000000
SQL> alter session set time_zone = 'Europe/London' ;
Session altered.
SQL> select to_timestamp('31-MAR-2013 01:00:00','DD-MON-YYYY HH24:MI:SS') from dual ;
TO_TIMESTAMP('31-MAR-201301:00:00','DD-MON-YYYYHH24:MI:SS')
---------------------------------------------------------------------------
31-MAR-13 01.00.00.000000000
SQL> select to_timestamp('31-MAR-2013 01:00:00','DD-MON-YYYY HH24:MI:SS') at time zone 'Europe/London' from dual ;
select to_timestamp('31-MAR-2013 01:00:00','DD-MON-YYYY HH24:MI:SS') at time zone 'Europe/London' from dual
*
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval
As you can see, when the clocks changed on the morning of 31st March, the time between 01:00:00 and 01:59:59 does not exist (as the clocks are advanced by one hour at 01:00:00 AM to 02:00:00 AM). However, the TO_TIMESTAMP function returns the "invalid" date.
But when I execute the same query (i.e. select to_timestamp('31-MAR-2013 01:00:00','DD-MON-YYYY HH24:MI:SS') from dual) against the same database using SQL Developer, I get "31-MAR-2013 02.00.00.000000000" as the result.
SESSIONTIMEZONE DBTIMEZONE
Europe/London +00:00
Can somebody help me understand this behaviour ?
Thanks in advance.
Edited by: user503699 on Apr 19, 2013 3:28 PM