1

Why does Oracle think that the timezone offset of Denver is UTC-7:00? The server time and systimestamp look right.

Server 1: Oracle 11.2.0.4.0

SELECT SESSIONTIMEZONE FROM DUAL;
    America/Denver

cat /etc/sysconfig/clock
    ZONE="America/Denver"

SELECT * FROM v$timezone_file;
    timezlrg_14.dat 14

SELECT systimestamp FROM dual;
    29-JUN-21 01.29.21.546036000 PM -06:00

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

Server 2: Oracle 11.1.0.6.0

SELECT SESSIONTIMEZONE FROM DUAL;
    America/Denver

cat /etc/sysconfig/clock
ZONE="America/Denver"
UTC=false
ARC=false

SELECT * FROM v$timezone_file;
    timezlrg.dat    4

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
Improve this question
3
  • What is systimestamp on server 2? Result of TZ_OFFSET depends on Daylight-Saving-Time status at the moment when you execute the function.
    – Wernfried Domscheit
    Jul 1, 2021 at 20:25
  • @WernfriedDomscheit systimestamp is the same on server 2. Here's a recent run on both servers, a few seconds apart: Server 1: 01-JUL-21 03.19.31.175264000 PM -06:00 Server 2: 01-JUL-21 03.19.37.524027000 PM -06:00
    – Charles Burns
    Jul 1, 2021 at 21:21
  • What is the result of TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:mi:SS tzr-tzd-tzh:tzm')? Perhaps the old time zone file (version 4 vs. version 14) does not define DST for America/Denver, however I did not find anything in the IANA time zone database releases notes.
    – Wernfried Domscheit
    Jul 2, 2021 at 6:35

1 Answer 1

Reset to default
2

In the IANA time zone database I found this comment:

# From Arthur David Olson (2005-08-22):
# Paul has been careful to use the "US" rules only in those locations
# that are part of the United States; this reflects the real scope of
# U.S. government action.  So even though the "US" rules have changed
# in the latest release, other countries won't be affected.

# Rule  NAME    FROM    TO  TYPE    IN  ON  AT  SAVE    LETTER/S
Rule    US  1918    1919    -   Mar lastSun 2:00    1:00    D
Rule    US  1918    1919    -   Oct lastSun 2:00    0   S
Rule    US  1942    only    -   Feb 9   2:00    1:00    W # War
Rule    US  1945    only    -   Aug 14  23:00u  1:00    P # Peace
Rule    US  1945    only    -   Sep lastSun 2:00    0   S
Rule    US  1967    2006    -   Oct lastSun 2:00    0   S
Rule    US  1967    1973    -   Apr lastSun 2:00    1:00    D
Rule    US  1974    only    -   Jan 6   2:00    1:00    D
Rule    US  1975    only    -   Feb 23  2:00    1:00    D
Rule    US  1976    1986    -   Apr lastSun 2:00    1:00    D
Rule    US  1987    2006    -   Apr Sun>=1  2:00    1:00    D
Rule    US  2007    max     -   Mar Sun>=8  2:00    1:00    D
Rule    US  2007    max     -   Nov Sun>=1  2:00    0   S

Looks like the DST rules changed in 2006/2007. I assume file timezlrg.dat version 4 refers still to the old rule.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.