Why are Oracle time zones wrong?

user_161816 0
Viewed 384

Why does Oracle have the wrong time zones? Chicago is off by an hour, Denver is off by an hour, etc.

The Linux server's time as well as Oracle's sessiontimezone and dbtimezone are correct.

Oracle's current_date does differ from sysdate by an hour. They should be the same.

SELECT tzabbrev, SUBSTR(tz_offset(tzname), 1, 6), tzname FROM v$timezone_names tz WHERE tzname = 'America/Denver';
LMT -07:00  America/Denver
MST -07:00  America/Denver
MWT -07:00  America/Denver
MDT -07:00  America/Denver

These should all be -06:00!

timezone-offset oracle11g timezone oracle
4 Answers

It doesn't:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM';

SELECT SYSTIMESTAMP AT TIME ZONE 'UTC',
       SYSTIMESTAMP AT TIME ZONE 'America/Denver'
FROM   DUAL;

Outputs:

SYSTIMESTAMPATTIMEZONE'UTC' SYSTIMESTAMPATTIMEZONE'AMERICA/DENVER'
2021-06-29 22:09:27.786125000 +00:00 2021-06-29 16:09:27.786125000 -06:00

If you do:

ALTER SESSION SET TIME_ZONE = 'America/Denver';

Then:

SELECT SYSTIMESTAMP, CURRENT_TIMESTAMP FROM DUAL;

Then the output on db<>fiddle (which is in the UK and currently on UTC+1):

SYSTIMESTAMP CURRENT_TIMESTAMP
2021-06-29 23:09:27.788275000 +01:00 2021-06-29 16:09:27.788278000 -06:00

I assume that your server time zone is set to UTC-7 and your session time zone is set to America/Denver (UTC-6) and you are using SYSTIMESTAMP which is getting the server time rather than the session time which CURRENT_TIMESTAMP would return.

db<>fiddle here

user_15092640
user_257140

Elimiate any issue with the 'current' time or with sneaky local replacements of the TZOFFSET. Check for a date that should be -6:00 and another that should be -7:00. Repeat for different years. I suspect that it doesn't switch between March 7th and 8th 2020, or Match 13th/14th 2021. There's an outside chance that the OS is supplying Oracle with an incorrect clock time (eg a UK time rather than UTC time)

with
    x as
        (select timestamp '2020-07-01 13:00:00 America/Denver' denver_time
        from dual)
select to_char(denver_time,'YYYY-MM-DD HH24:MI TZH:TZM') denver_time,
         to_char(denver_time at time zone 'UTC','YYYY-MM-DD HH24:MI TZH:TZM') utc_time,
         to_char(denver_time at time zone dbtimezone,'YYYY-MM-DD HH24:MI TZH:TZM') db_time,
     dbtimezone, sessiontimezone
from x;

with
    x as
        (select timestamp '2020-03-01 13:00:00 America/Denver' denver_time
        from dual)
select to_char(denver_time,'YYYY-MM-DD HH24:MI TZH:TZM') denver_time,
         to_char(denver_time at time zone 'UTC','YYYY-MM-DD HH24:MI TZH:TZM') utc_time,
         to_char(denver_time at time zone dbtimezone,'YYYY-MM-DD HH24:MI TZH:TZM') db_time,
         dbtimezone, sessiontimezone
from x;
user_257140

Daylight savings time? SessionTimeZone comes from the session settings rather than the database timezone settings.

user_85987750
Related