This discussion is archived
4 Replies Latest reply: Aug 16, 2013 2:59 PM by Gusora RSS

Bit confused over time zones

Gusora Newbie
Currently Being Moderated

I am running the DB on my machine at home. I have some queries regarding time and time zones....

 

SQL> select dbtimezone from dual;

+00:00

So I take it this is UTC, time zone 0:00

 

SQL> select to_char(sysdate, 'hh:mi:ss') from dual;

05:43:53

So why is this 1 hour ahead of UTC time when I check it on the internet

 

SQL> select sessiontimezone from dual;

+01:00

We are London/GMT with DST active so this makes sense.

 

SQL> select to_char(current_date, 'hh:mi:ss') from dual;

05:44:30

1 hour ahead of UTC, as expected.

 

I would have expected the DB and session time to differ by 1 hour since the DB time zone is UTC and the session is UTC +1:00 (due to DST).

 

When I check the server time its 1 hour ahead of UTC yet the time zone is +0:00....confused.

  • 1. Re: Bit confused over time zones
    Ishan Journeyer
    Currently Being Moderated

    May be your database was created with default timezone. Check out the excerpt from Oracle Documentation.

     

    Setting the Database Time Zone

    Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. If you do not set the database time zone, then it defaults to the time zone of the server's operating system.

    The time zone may be set to an absolute offset from UTC or to a named region. For example, to set the time zone to an offset from UTC, use a statement similar to the following:

    CREATE DATABASE db01 ... SET TIME_ZONE='-05:00';  

    The range of valid offsets is -12:00 to +14:00.

     

    Ishan

  • 2. Re: Bit confused over time zones
    sb92075 Guru
    Currently Being Moderated

    > select to_char(sysdate, 'hh:mi:ss') from dual;

    SYSDATE  is not timezone aware & reports based  upon what is obtained from the Operating System upon which  Oracle DB is installed.

  • 3. Re: Bit confused over time zones
    Alvaro Pro
    Currently Being Moderated

    SQL> select to_char(sysdate, 'hh:mi:ss') from dual;

    05:43:53

    So why is this 1 hour ahead of UTC time when I check it on the internet

     

    As SB has pointed out, sysdate ignores any Time zone settings. It simply issues a system call to the OS to return it's time to you.

     

    One way to get the time with time zone applies is using CURRENT_DATE instead of sysdate. sysdate always returns the time of the OS the DB is hosted on.

  • 4. Re: Bit confused over time zones
    Gusora Newbie
    Currently Being Moderated

    SQL> select dbtimezone from dual;

    +00:00

    From this I can see that my server's time zone is UTC, correct?


    If you do not set the database time zone, then it defaults to the time zone of the server's operating system.


    Which is what's happening here. Its defaulted to UTC.

     

     

    I ran this statement at 4:43:53 UTC .....or 5:43:53 local time.

    SQL> select to_char(sysdate, 'hh:mi:ss') from dual;

    05:43:53

    Okay my thinking was .......My OS knows I'm UTC with DST active but shouldn't the DB server should be reporting the time according to UTC not UTC +01:00.

     

    I found this in the documentation and coincides with the OP by SB (thank you)

    SYSDATE returns the current date and time set for the operating system on which the database resides.

Legend

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