RE: EST to UTC on time change

"Current Timezone" only returns the current time adjustment.
It will not return the time adjustment that was active previously, say 2 months ago.


I can think of three ways to save the timestamp that will allow an accurate conversion.
1. Convert to UTC before saving it.
2. Save TIMEZONE Offset for timestamp. (The +/- 500)
3. Save ST/DST indicator for timestamp. (0/1 based on standard time/daily savings time being active)


IMO, To accurately represent all times it would be best practice to convert the timestamp to UTC before saving it in the database.

If the timestamp is not converted to UTC, then the only way to accurately represent all times would be to save additional information about the timestamp.
Like the timestamp's time zone offset or whether ST/DST was active for the timestamp.


Next you would need to use QWCCVTDT to convert the date/time to another timezone.
Creating an SQL wrapping function would work. Someone might have already done that.


QWCCVTDT can convert most times without the timezone offset or the ST/DST indicator.
That is because the TIMZON reference already knows the desired ST/DST period. For example 'QN0500EST3' would be the current reference for EST time changing between March and November.


Only the times during the "Fall Back" time period need to have special consideration. That is the 1am-2am time that repeats itself when the time change occurs.

The only way to account for the overlapped time would be to:

1. Convert to UTC before saving it.
2. Save TIMEZONE Offset for timestamp
3. Save ST/DST indicator for timestamp.


So if you are not concerned with the double 1-2am then just pass the timestamp to QWCCVTDT and convert it from 'QN0500EST3' to ' Q0000UTC'.



Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mitch Gallman
Sent: Tuesday, November 27, 2018 12:32 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: EST to UTC on time change

Looking for thoughts on handling the time changes when converting EST to UTC via SQL with the current timezone register: <timestamp> - current timezone

Prior to the most recent time change we sent 11/5/2018 18:00Z for 2 pm since the offset was -4.
After the time change we send 11/5/2018 19:00Z for 2 pm since the offset was then -5.

How can I get/adjust the timezone offset based on the future (or past) target date/time?

Thanks,

Mitch