Oracle 10g Time Zone Confusion

Question

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI')
      ,TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI')
      ,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') 
      ,TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') 
      ,TO_CHAR(LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') 
      ,DBTIMEZONE
      ,SESSIONTIMEZONE
  FROM DUAL;

Is returning:

2012-01-16 11:42
2012-01-16 11:42    
2012-01-16 11:42 -06:00 
2012-01-16 11:42 -06:00 
2012-01-16 11:42 +00:00 
+00:00  
-06:00

It seems to think the database time zone is GMT, yet the SYSDATE is the same as the CURRENT_DATE.

When I remote into that server (Windows), the time zone is apparently CST (however, I am aware that this could be picking up my Terminal Services Client Time Zone Offset, but this machine doesn't have Terminal Services on it, just administrative)

Running the same thing against a server in Amsterdam (4 minutes later all from the same TOAD client), I'm getting:

2012-01-16 18:46
2012-01-16 11:46    
2012-01-16 18:46 +01:00 
2012-01-16 11:46 -06:00 
2012-01-16 11:46 +00:00 
+02:00  
-06:00

Note the +2, but at least the SYSDATE and CURRENT_DATE are differing.

What is going on here? Where does SYSDATE come from and is there anything else which affects it?

It seems like DBTIMEZONE is not used for any of these things? So what is DBTIMEZONE used for?

Answer 1

There are actually 3 timezones here, not 2

• the timezone of the session/client
  • Shown in SESSIONTIMEZONE
  • This is the timezone of CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP. The difference between those 3 is the return type, they return a DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE respectively)
• The database timezone
  • Shown in DBTIMEZONE
  • This is the the timezone used for the internal storage of TIMESTAMP WITH LOCAL TIME ZONE values. Note that values are converted to/from session timezone on insert/select so it actually isn't as important as it seems
  • This is NOT the timezone of SYSDATE/SYSTIMESTAMP
• The database OS timezone
  • In unix, it is based on the TZ variable when Oracle is started
  • This is the timezone of SYSDATE and SYSTIMESTAMP

In your first example, I can see that the session TZ is UTC-6, the database TZ is UTC, and the database OS timezone is UTC-6.

In your second example, I can see that the session TZ is UTC-6, the database TZ is UTC+2, and the database OS timezone is UTC+1.

Answer 2

The details are in the fine print of the documentation. Take a look at the Return Type, and the actual Timezone the DATE or TIMESTAMP is calculated in.

1. SYSDATE
   • Return Type: DATE
   • Time Zone: Host OS of Database Server
2. CURRENT_DATE
   • Return Type: DATE
   • Time Zone: Session
3. SYSTIMESTAMP
   • Return Type: TIMESTAMP WITH TIME ZONE
   • Time Zone: Host OS of Database Server
4. CURRENT_SYSTIMESTAMP
   • Return Type: TIMESTAMP WITH TIME ZONE
   • Time Zone: Session
5. LOCALTIMESTAMP
   • Return Type: TIMESTAMP
   • Time Zone: Session
6. DBTIMEZONE
   • Time Zone: DB Time Zone. Inherits from DB Server OS, but can be overridden using set at DB Creation or Alter using TIME_ZONE DB Parameter (SET TIME_ZONE=...). This affects the time zone used for TIMESTAMP WITH LOCAL TIME ZONE datataypes.
7. SESSIONTIMEZONE
   • Time Zone: Session Timezone. Inherits from Session hosting OS, but can be overridden using ALTER SESSION (ALTER SESSION SET TIME_ZONE=...).

Return Type, indicates whether or not the Timezone is available within the Datatype. If you try to print TZR if datatype does not carry TimeZone, then it will just show up as +00:00 (doesn't mean it is GMT). Otherwise It will show the TimeZone matching either the Database or Session as indicated.

Time Zone, indicates in which Timezone the time is calculated. For matching TimeZone, the same Date/Time will be shown (HH24:MI).

Note that none of the FUNCTIONS return TIME in the Time Zone set with the DB TIME_ZONE (or as returned by the DBTIMEZONE function). That is, none of the functions also return a datatype of TIMESTAMP WITH LOCAL TIME ZONE. Howver you can convert the output of any of the functions that does return a timezone into a different timezone (including DBTIMEZONE) as follows:

SELECT SYSTIMESTAMP AT TIME ZONE DBTIMEZONE FROM DUAL;

More information on my blog.

Answer 3

Use UTC time and offset your timezone from UTC, To get UTC in Oracle use SYS_EXTRACT_UTC

Convert SYSTEMDATE to UTC

    select sys_extract_utc(systimestamp) from dual;

As for the difference the definition from Oracle documentation might help to explain:

• LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP
• CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE
• SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides
• CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
• SYSDATE returns the current date and time set for the operating system on which the database resides.
• DBTIMEZONE returns the value of the database time zone.