This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Apr 22, 2013 12:48 PM by user503699 RSS

Daylight Savings Time Confusion

user503699 Expert
Currently Being Moderated
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
  • 1. Re: Daylight Savings Time Confusion
    Fran Guru
    Currently Being Moderated
    i was trying to recreate your issue in my own database and i check:
    SQL> SELECT * from V$TIMEZONE_NAMES WHERE TZNAME LIKE '%London%';
    
    
    TZNAME                                                           TZABBREV
    ---------------------------------------------------------------- ----------
    -------------------
    Europe/London                                                    LMT
    Europe/London                                                    GMT
    Europe/London                                                    BST
    Europe/London                                                    BDST
    So it make me think, are you using the same?

    HTH
  • 2. Re: Daylight Savings Time Confusion
    user503699 Expert
    Currently Being Moderated
    Fran wrote:
    So it make me think, are you using the same?
    I guess yes.
    SQL> SELECT * from V$TIMEZONE_NAMES WHERE TZNAME LIKE '%London%';
    
    TZNAME
    ----------------------------------------------------------------
    TZABBREV
    ----------------------------------------------------------------
    Europe/London
    LMT
    
    Europe/London
    GMT
    
    Europe/London
    BST
    
    
    TZNAME
    ----------------------------------------------------------------
    TZABBREV
    ----------------------------------------------------------------
    Europe/London
    BDST
  • 3. Re: Daylight Savings Time Confusion
    Fran Guru
    Currently Being Moderated
    sorry, i don't explain it very well.

    I mean that the same time_zone name can be the same, but the value depend of the time zone table:

    For example:
    TZNAME                                              TZABBREV
    ---------------------------------------------------------------- ----------
    
    Europe/London                                                    LMT
    Europe/London                                                    GMT
    Europe/London                                                    BST
    Europe/London                                                    BDST
    LMT
     Local Mean Time. This is the 'natural' mean solar time for the time zone, excluding the equation of time. At the moments where the equation of time is zero, this is the amount of time to be added to UTC. Right then the sun passes the local meridian and is at its highest point in the sky.
    This means that the sun is at its highest point at local time:
    12 + zonetime + DST - LMT - EquationOfTime
    Example: Kaxgar, China; location: 39:29°N, 075:59°E; LMT: 5:03:56; zone: 8; DST: 0; EoT on 2005-07-29: -0:06:28; this gives local time 15:02:32.
    Over a year the equation of time can make a difference of between about + and - 18 minutes with the mean LMT time. The LMT time is calculated from the geographic longitude of a local place. Divide the number of degrees in longitude by 15.
    GMT:
    Greenwich Mean Time (GMT) is a time system originally referring to mean solar time at the Royal Observatory in Greenwich, London,
    BST:
    British Summer Time
    Time zone offset: UTC + 1 hour
    BST is 1 hour ahead of Coordinated Universal Time (UTC)
    Note that BST is a daylight saving time/summer time zone. It is generally only used during the summer in the places listed below, during the winter GMT or IST are used instead
    *BDST i cant find.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 4. Re: Daylight Savings Time Confusion
    User477708-OC Journeyer
    Currently Being Moderated
    bdst is british double summer time

    https://en.wikipedia.org/wiki/British_Summer_Time#Periods_of_deviation


    I think the format is slightly different, need to use AT TIME ZONE as....

    SELECT CAST((FROM_TZ(CAST(TO_DATE('31-MAR-2013 01:00:00','DD-MON-YYYY HH24:MI:SS') AS TIMESTAMP), 'GMT')
    AT TIME ZONE 'Europe/London') AS DATE)
    FROM DUAL;
  • 5. Re: Daylight Savings Time Confusion
    user503699 Expert
    Currently Being Moderated
    961469 wrote:
    bdst is british double summer time

    https://en.wikipedia.org/wiki/British_Summer_Time#Periods_of_deviation


    I think the format is slightly different, need to use AT TIME ZONE as....
    I understand that but my question is why, in SQL Developer, I directly get the "adjusted" time as a result of TO_TIMESTAMP function whereas in SQL*Plus the time is not adjusted.
  • 6. Re: Daylight Savings Time Confusion
    User477708-OC Journeyer
    Currently Being Moderated
    Isnt there a user timezone setting in sqldeveloper? dont have it on this client. its in properties somewhere....guess thats having an affect.
  • 7. Re: Daylight Savings Time Confusion
    user503699 Expert
    Currently Being Moderated
    961469 wrote:
    Isnt there a user timezone setting in sqldeveloper? dont have it on this client. its in properties somewhere....guess thats having an affect.
    Not sure what is a "user timezone" but if there was one, it would reflect as "sessiontimezone". As mentioned earlier, querying session timezone produces the same result i.e. 'Europe/London'.
  • 8. Re: Daylight Savings Time Confusion
    Fran Guru
    Currently Being Moderated
    i did the same proccess like you in sql*plus and sqldeveloper (Versión 3.1.07) and got the same result in both.
    Probably your configuration at sqldeveloper is the cause.
  • 9. Re: Daylight Savings Time Confusion
    User477708-OC Journeyer
    Currently Being Moderated
    try this to see if it works

    http://stackoverflow.com/questions/14864015/how-to-change-the-timezone-to-oracle-sql-developer-oracle-data-modeler
  • 10. Re: Daylight Savings Time Confusion
    user503699 Expert
    Currently Being Moderated
    961469 wrote:
    try this to see if it works

    http://stackoverflow.com/questions/14864015/how-to-change-the-timezone-to-oracle-sql-developer-oracle-data-modeler
    Thanks but I am not really convinced that result returned by SQL Developer is incorrect. In fact, it appears to be correct and the one in SQL*Plus appears to be not correct (as it returns an "invalid" time).
    As a first step, I am trying to understand why I am getting 2 different results from 2 different tools.
  • 11. Re: Daylight Savings Time Confusion
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user503699 wrote:
    I am trying to understand how Daylight Savings Time is handled in Oracle Database.
    Me, too, and it takes a lot of investigation.

    I believe to_timestamp() converts a text to a timestamp without consideration of any time zone.
    Follow-up tests for you:
    a) investigate the effects of to_timestamp_tz() instead of to_timestamp()
    b) investigate the effects of sys_extract_utc() on both versions of the timestamp calls

    I would also be inclined to use 1:30 am as the test time to avoid getting confused about boundaries.

    Is your SQL*Plus session local to the server, with SQL*Developer coming in across the network. If so it looks as if Oracle is treating the SQL*Developer session as if it were running in UTC/GMT rather than BST, despite your attempt to change the session time zone.

    Regards
    Jonathan Lewis
  • 12. Re: Daylight Savings Time Confusion
    jgarry Guru
    Currently Being Moderated
    Check the settings in developer and see if things change if you set it to skip nls settings. http://www.thatjeffsmith.com/archive/2012/02/oracle-date-datatype-should-be-datetime-datatype/
  • 13. Re: Daylight Savings Time Confusion
    user503699 Expert
    Currently Being Moderated
    Jonathan Lewis wrote:
    user503699 wrote:
    I am trying to understand how Daylight Savings Time is handled in Oracle Database.
    Me, too, and it takes a lot of investigation.
    If that is some kind of taunt, then it was not really called for.
    I believe to_timestamp() converts a text to a timestamp without consideration of any time zone.
    Follow-up tests for you:
    a) investigate the effects of to_timestamp_tz() instead of to_timestamp()
    b) investigate the effects of sys_extract_utc() on both versions of the timestamp calls

    I would also be inclined to use 1:30 am as the test time to avoid getting confused about boundaries.
    Here are the details from the suggested tests (as well as some more that I think might be relevant):
    From SQL*Plus:
    SQL> select * from v$version ;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> select name from v$database ;
    
    NAME
    ---------
    ORCL
    
    SQL> show user ;
    USER is "HR"
    SQL> select sessiontimezone, dbtimezone from dual ;
    
    SESSIONTIMEZONE                                                             DBTIME
    --------------------------------------------------------------------------- ------
    Europe/London                                                               +00:00
    
    SQL> column parameter format a40
    SQL> column value format a40
    SQL> select * from nls_session_parameters ;
    
    PARAMETER                                VALUE
    ---------------------------------------- ----------------------------------------
    NLS_LANGUAGE                             ENGLISH
    NLS_TERRITORY                            UNITED KINGDOM
    NLS_CURRENCY                             #
    NLS_ISO_CURRENCY                         UNITED KINGDOM
    NLS_NUMERIC_CHARACTERS                   .,
    NLS_CALENDAR                             GREGORIAN
    NLS_DATE_FORMAT                          DD-MON-YYYY HH:MI:SS AM
    NLS_DATE_LANGUAGE                        ENGLISH
    NLS_SORT                                 BINARY
    NLS_TIME_FORMAT                          HH24.MI.SSXFF
    NLS_TIMESTAMP_FORMAT                     DD-MON-YYYY HH:MI:SSXFF AM
    NLS_TIME_TZ_FORMAT                       HH24.MI.SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-YYYY HH:MI:SSXFF TZR
    NLS_DUAL_CURRENCY                        ?
    NLS_COMP                                 BINARY
    NLS_LENGTH_SEMANTICS                     BYTE
    NLS_NCHAR_CONV_EXCP                      FALSE
    
    17 rows selected.
    
    SQL> select to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
    
    TO_TIMESTAMP('31-MAR-201301:30:00','DD-MON-YYYYHH24:MI:SS')
    ---------------------------------------------------------------------------
    31-MAR-2013 01:30:00.000000000 AM
    
    SQL> select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
    select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual
                           *
    ERROR at line 1:
    ORA-01878: specified field not found in datetime or interval
    
    
    SQL> select sys_extract_utc(to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS')) from dual ;
    select sys_extract_utc(to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS')) from dual
                                                                                               *
    ERROR at line 1:
    ORA-01878: specified field not found in datetime or interval
    
    
    SQL> select sys_extract_utc(to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS')) from dual ;
    select sys_extract_utc(to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS')) from dual
                                           *
    ERROR at line 1:
    ORA-01878: specified field not found in datetime or interval
    
    
    SQL> !date
    Mon Apr 22 10:09:33 BST 2013
    
    SQL> !whoami
    oracle
    
    SQL> !cat /etc/sysconfig/clock
    # The ZONE parameter is only evaluated by system-config-date.
    # The timezone of the system is defined by the contents of /etc/localtime.
    ZONE="Europe/London"
    UTC=true
    ARC=false
    From SQL Developer:
    select sessiontimezone, dbtimezone from dual ;
    
    SESSIONTIMEZONE     DBTIMEZONE
    Europe/London     +00:00
    
    select * from nls_session_parameters ;
    
    PARAMETER     VALUE
    NLS_LANGUAGE     ENGLISH
    NLS_TERRITORY     UNITED KINGDOM
    NLS_CURRENCY     £
    NLS_ISO_CURRENCY     UNITED KINGDOM
    NLS_NUMERIC_CHARACTERS     .,
    NLS_CALENDAR     GREGORIAN
    NLS_DATE_FORMAT     DD-MON-YYYY HH.MI.SS AM
    NLS_DATE_LANGUAGE     ENGLISH
    NLS_SORT     BINARY
    NLS_TIME_FORMAT     HH24.MI.SSXFF
    NLS_TIMESTAMP_FORMAT     DD-MON-YYYY HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT     HH24.MI.SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT     DD-MON-YYYY HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY     €
    NLS_COMP     BINARY
    NLS_LENGTH_SEMANTICS     BYTE
    NLS_NCHAR_CONV_EXCP     FALSE
    
    select to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
    TO_TIMESTAMP('31-MAR-201301:30:00','DD-MON-YYYYHH24:MI:SS')
    31-MAR-2013 02.30.00.000000000 AM
    
    select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
    ORA-01878: specified field not found in datetime or interval
    01878. 00000 -  "specified field not found in datetime or interval"
    *Cause:    The specified field was not found in the datetime or interval.
    *Action:   Make sure that the specified field is in the datetime or interval.
    
    select sys_extract_utc(to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS')) from dual ;
    ORA-01878: specified field not found in datetime or interval
    01878. 00000 -  "specified field not found in datetime or interval"
    *Cause:    The specified field was not found in the datetime or interval.
    *Action:   Make sure that the specified field is in the datetime or interval.
    
    select sys_extract_utc(to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS')) from dual ;
    ORA-01878: specified field not found in datetime or interval
    01878. 00000 -  "specified field not found in datetime or interval"
    *Cause:    The specified field was not found in the datetime or interval.
    *Action:   Make sure that the specified field is in the datetime or interval.
    >
    Is your SQL*Plus session local to the server, with SQL*Developer coming in across the network. If so it looks as if Oracle is treating the SQL*Developer session as if it were running in UTC/GMT rather than BST, despite your attempt to change the session time zone.
    Both SQL*Plus and SQL Developer were run locally on the DB server.
  • 14. Re: Daylight Savings Time Confusion
    user503699 Expert
    Currently Being Moderated
    jgarry wrote:
    Check the settings in developer and see if things change if you set it to skip nls settings. http://www.thatjeffsmith.com/archive/2012/02/oracle-date-datatype-should-be-datetime-datatype/
    Joel,

    Thanks.
    I tried by skipping NLS settings in SQL Developer but results were the same.
    At this stage, I am thinking the results returned by SQL Developer (time adjusted to BST) is the expected behavior (at least for me). But I am really confused as to how does SQL Developer manage to do that. I have a strong feeling that the SQL Developer "behavior" is due to some jdk/jdbc configuration but I don't know enough. But I am not able to get that behavior in SQL*Plus. What can I change to make SQL*Plus (and any other client) to return same results as SQL Developer ?
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points