1

I am not getting correct timezone in Oracle Database. The issue is happening only starting 2021. Please see two same queries - first one for year 2020 and second for year 2021. The first one is correctly giving the timezone as EDT; the second one is incorrectly giving EST. How to fix this?

SELECT 
   TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE 
FROM DUAL ;

---
EDT

SELECT 
   TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE 
FROM DUAL ;

---
EST
  • Would you believe - after meetings problem in fall of 2020 following the shift to Standard time, Trump moves to abolish DST? – RDFozz Jul 5 '18 at 17:36
6

I did not investigate any further for specific database and timezone file versions, but I guess you use and old database version with an outdated timezone file.

10.2.0.5.0:

SQL> select VERSION from v$timezone_file;

   VERSION
----------
         4

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EST

11.2.0.4.180417:

SQL> select VERSION from v$timezone_file;

   VERSION
----------
        14

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

It's time to upgrade.

This is a good starting point (requires a valid Oracle Support contract which you would need anyway for downloading the patch as well): Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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