Oracle – Fixing Incorrect EST Timezone Instead of EDT

oracletimezone

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

Best Answer

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)

Related Question