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;

Returns:

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

The database timezone seems to be GMT, but SYSDATE is the same as CURRENT_DATE.

When I retire to this server (Windows), the time zone seems to be CST (however, I know that this may be due to disabling the time zone of the Terminal Services client, but there are no Terminal Services on this computer, just administrative)

By running the same thing on a server in Amsterdam (after 4 minutes everything from the same TOAD client), I get:

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 +2, but at least SYSDATE and CURRENT_DATE are different.

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

Does DBTIMEZONE seem to be not used for any of these things? So what is DBTIMEZONE used for?

Answer 1

3 , 2

• /
  • SESSIONTIMEZONE
  • CURRENT_DATE, LOCALTIMESTAMP CURRENT_TIMESTAMP. - , DATE, TIMESTAMP TIMESTAMP WITH TIME ZONE ).
• • DBTIMEZONE
  • , TIMESTAMP WITH LOCAL TIME ZONE. , / insert/select, , .
  • SYSDATE/SYSTIMESTAMP
• • unix TZ Oracle.
  • SYSDATE SYSTIMESTAMP

, TZ - UTC-6, TZ - UTC, - UTC-6.

, TZ - UTC-6, TZ - UTC + 2, - UTC + 1.

Answer 2

. , DATE TIMESTAMP.

• SYSDATE
  • : DATE
  • : -
• CURRENT_DATE
  • : DATE
  • :
• SYSTIMESTAMP
  • : TIMESTAMP WITH TIME ZONE
  • : -
• CURRENT_SYSTIMESTAMP
  • : TIMESTAMP WITH TIME ZONE
  • :
• LOCALTIMESTAMP
  • : TIMESTAMP
  • :
• DBTIMEZONE
  • : DB. , TIME_ZONE DB (SET TIME_ZONE =...). , TIMESTAMP WITH LOCAL TIME ZONE.
• SESSIONTIMEZONE
  • : . Session Host, ALTER SESSION (ALTER SESSION SET TIME_ZONE =...).

, , . TZR, TimeZone, +00: 00 ( , GMT). TimeZone, , , .

, , . TimeZone / (HH24: MI).

, FUNCTIONS TIME , DB TIME_ZONE ( DBTIMEZONE). , TIMESTAMP . , ( DBTIMEZONE) :

SELECT SYSTIMESTAMP AT TIME ZONE DBTIMEZONE FROM DUAL;

.

Answer 3

UTC UTC, UTC Oracle, SYS_EXTRACT_UTC

SYSTEMDATE UTC

    select sys_extract_utc(systimestamp) from dual;

, Oracle :

• LOCALTIMESTAMP TIMESTAMP
• CURRENT_TIMESTAMP , TIMESTAMP WITH TIME ZONE
• SYSTIMESTAMP , , , .
• CURRENT_DATE , Datatype DATE.
• SYSDATE , , .
• DBTIMEZONE .