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.