Oracle - How to *correctly* convert from EST/EDT to GMT?

Question

I have a query that is doing some time zone conversions. As an example, I want to convert from an EST time to GMT:

For the EDT time of 3/13/2016 @2:00 AM (immediately following the EST->EDT changeover) I should get a GMT time of 3/13/2016 7:00:00 AM (verified here for the date of 3/13/2016 @2am). Instead I am getting 3/13/2016 6:00:00 AM from using this query:

SELECT NEW_TIME(TO_DATE('2016/3/13 02:00:00 AM', 
               'YYYY/MM/DD HH:MI:SS AM'),'EDT','GMT') 
FROM DUAL;

For the EST time of 3/13/2016 @1:00AM (one second before the changeover) I am getting what appears to be the correct result:

SELECT NEW_TIME(TO_DATE('2016/3/13 01:59:59 AM', 
                        'YYYY/MM/DD HH:MI:SS AM'),'EST','GMT') 
FROM DUAL;

Result:

3/13/2016 6:59:59 AM

What am I doing wrong here? I have read the Oracle documentation on NEW_TIME and have tried switching the EDT/EST with the GMT (based on the one example they have at the bottom of the page) but that gives me even more odd results.

Answer 1

I ended up using:

SELECT SYSDATE+(SUBSTR(TO_CHAR(SYSTIMESTAMP, 'TZR'),2,2))/24  
FROM DUAL;

where (SUBSTR(TO_CHAR(SYSTIMESTAMP, 'TZR'),2,2)) provides you with the current offset from utc time. I agree that FROM_TZ is probably more accurate in the many edge cases that exist with timezone but we were really looking for something that would calculate quickly when embedding this into a view.

To properly convert between zones, I simply added the current offset to whatever time it was that I needed to convert:

DECLARE 
l_convert_me_to_utc DATE := TO_DATE('9/12/2016 1:00:00 PM','MM/DD/YYYY HH12:MI:SS AM');
l_converted DATE;
BEGIN
    l_converted:=(l_convert_me_to_utc+(SUBSTR(TO_CHAR(SYSTIMESTAMP, 'TZR'),2,2))/24);
    DBMS_OUTPUT.put_line ('UTC TIME IS: '||TO_CHAR(l_converted, 'MM/DD/YYYY HH12:MI:SS AM'));    
END;

UTC TIME IS: 09/12/2016 05:00:00 PM

Which makes sense since right now I am in NY TZ in EDT with a -4 offset from UTC

EDIT: As per the comments below, this really only works if you are converting whatever your OS SYSDATE is right now to UTC. If it's some other timezone or some time in the past or future this does not really work. My final logic was more complex than what you see above. For my application and with more complex logic (checking a table with all TZ switch dates from now till 2100), this really DOES work. Just don't take what I have above and assume it converts things to UTC perfectly as is.

Answer 2

You should not use the old fashion function NEW_TIME, use FROM_TZ and Datetime Expressions as recommended by Oracle.

One problem is time time zones EST or EDT do not have any daylight saving! EST always means (most likely) UTC-05:00 no matter if summer of winter. EDT always means (most likely) UTC-04:00 no matter if summer of winter.

Apart from missing daylight saving support, EST or EDT are ambiguous, see this list:

SELECT tzabbrev, TZ_OFFSET(tzname), tzname
FROM V$TIMEZONE_NAMES
WHERE tzname IN ('EST', 'EDT') OR tzabbrev IN ('EST', 'EDT')
ORDER BY 1,2;

TZABBREV    TZ_OFFSET(TZNAME)   TZNAME
===============================================
EDT -04:00  America/Detroit
EDT -04:00  US/Eastern
EDT -04:00  US/East-Indiana
EDT -04:00  EST5EDT
EDT -04:00  Canada/Eastern
EDT -04:00  America/Toronto
EDT -04:00  America/Thunder_Bay
EDT -04:00  America/Santo_Domingo
EDT -04:00  America/Pangnirtung
EDT -04:00  America/Nipigon
EDT -04:00  America/New_York
EDT -04:00  America/Nassau
EDT -04:00  America/Montreal
EDT -04:00  America/Louisville
EDT -04:00  America/Kentucky/Monticello
EDT -04:00  America/Kentucky/Louisville
EDT -04:00  America/Iqaluit
EDT -04:00  America/Indianapolis
EDT -04:00  America/Indiana/Winamac
EDT -04:00  America/Indiana/Vincennes
EDT -04:00  America/Indiana/Vevay
EDT -04:00  America/Indiana/Petersburg
EDT -04:00  America/Indiana/Marengo
EDT -04:00  US/Michigan
EDT -04:00  America/Fort_Wayne
EDT -04:00  America/Grand_Turk
EDT -04:00  America/Indiana/Indianapolis
EDT -05:00  America/Jamaica
EDT -05:00  America/Indiana/Tell_City
EDT -05:00  Jamaica
EDT -05:00  America/Cancun
EDT -05:00  America/Port-au-Prince
EST +09:30  Australia/Broken_Hill
EST +09:30  Australia/Yancowinna
EST +10:00  Australia/Canberra
EST +10:00  Australia/Brisbane
EST +10:00  Australia/ACT
EST +10:00  Australia/Currie
EST +10:00  Australia/Hobart
EST +10:00  Australia/Lindeman
EST +10:00  Australia/Melbourne
EST +10:00  Australia/NSW
EST +10:00  Australia/Queensland
EST +10:00  Australia/Tasmania
EST +10:00  Australia/Sydney
EST +10:00  Australia/Victoria
EST +10:30  Australia/Lord_Howe
EST +10:30  Australia/LHI
EST +11:00  Antarctica/Macquarie
EST -03:00  America/Moncton
EST -04:00  America/Antigua
EST -04:00  America/Detroit
EST -04:00  America/Fort_Wayne
EST -04:00  America/Grand_Turk
EST -04:00  America/Indiana/Indianapolis
EST -04:00  America/Indiana/Marengo
EST -04:00  America/Indiana/Petersburg
EST -04:00  America/Indiana/Vevay
EST -04:00  America/Indiana/Vincennes
EST -04:00  America/Indiana/Winamac
EST -04:00  America/Indianapolis
EST -04:00  America/Iqaluit
EST -04:00  America/Kentucky/Louisville
EST -04:00  America/Kentucky/Monticello
EST -04:00  America/Louisville
EST -04:00  America/Montreal
EST -04:00  America/Nassau
EST -04:00  America/New_York
EST -04:00  America/Nipigon
EST -04:00  America/Pangnirtung
EST -04:00  America/Santo_Domingo
EST -04:00  America/Thunder_Bay
EST -04:00  America/Toronto
EST -04:00  Canada/Eastern
EST -04:00  EST5EDT
EST -04:00  US/East-Indiana
EST -04:00  US/Eastern
EST -04:00  US/Michigan
EST -05:00  US/Central
EST -05:00  Jamaica
EST -05:00  America/Cancun
EST -05:00  America/Cayman
EST -05:00  America/Chicago
EST -05:00  America/Coral_Harbour
EST -05:00  America/Indiana/Knox
EST -05:00  America/Indiana/Tell_City
EST -05:00  America/Jamaica
EST -05:00  America/Knox_IN
EST -05:00  America/Atikokan
EST -05:00  America/Menominee
EST -05:00  America/Merida
EST -05:00  America/Panama
EST -05:00  America/Port-au-Prince
EST -05:00  America/Rankin_Inlet
EST -05:00  America/Resolute
EST -05:00  CST
EST -05:00  EST
EST -05:00  US/Indiana-Starke
EST -06:00  America/Managua
EST -06:00  America/Cambridge_Bay

Try it like this, then your times will automatically converted properly regardless of your seasons:

SELECT 
    FROM_TZ(TO_TIMESTAMP('2016/January/01 12:00:00 AM', 'YYYY/MONTH/DD HH:MI:SS AM'), 'America/New_York') AT TIME ZONE 'UTC',
    FROM_TZ(TO_TIMESTAMP('2016/July/01 12:00:00 AM', 'YYYY/MONTH/DD HH:MI:SS AM'), 'America/New_York') AT TIME ZONE 'UTC'
FROM dual;