Re: EST to UTC on time change

Unfortunately it's more complex than that. With the question being "How can
I get/adjust the timezone offset based on the future (or past) target
date/time?" we really need to know the year portion of the date (and
actually more than just that as we'll get to (if I remember)).

Currently (2018) DST, for most of the United States, starts on the 2nd
Sunday of March and ends on the 1st Sunday of November. But this is only
due to current law and can be changed at any time at either the federal
or state level. If you know the year is greater than or equal to 2007 and
less than 2019 then you could, using SQL, calculate the start and end
dates. You would however need a different calculation for earlier years:

1987 thru 2006 - DST starts on the 1st Sunday of April, ends on the last
Sunday of October
1975 thru 1986 - DST starts on last Sunday of April, ends on the last
Sunday of October
1974 thru 1975 - DST starts on January 6 1974, ends on April 27 1975 (that
is, DST year around). This was later amended to end DST on October 27 1974
and resume DST on February 23 1975.
1967 thru 1972 - DST starts on last Sunday of April, ends last Sunday of
October
Prior to 1967 just trust me, you don't want to know...

Now to complicate things some states do not observe DST. Arizona (with the
exception of the Navajo Nation) does not generally observe DST. The one
exception, so far, is 1967 when the Arizona legislature neglected to
exclude Arizona from the federal DST rules. Michigan prior to 1972 did not
observer DST but does observe DST starting in 1972. Hawaii (along with the
non-states American Samoa, Puerto Rico, and the Virgin Islands) also does
not observe DST. Hawaii did however observe DST as late as 1945. Alaska,
since 2015, has been debating whether or not to observe DST (currently
does). California is currently (motion passed in 2018) evaluating going to
DST on a year around basis.

So clearly, in addition to knowing the year and time zone you also need to
know the state and in some cases a specific portion of a state. You
mentioned for instance being in EST. I assume you are not in Indiana as
that state supports both CST and EST. And, getting back to the portion of a
state point, prior to 2006 most (but not all) of Indiana did not observe
DST.

And we haven't even looked at countries other than the United States...

For any given year and geographic location you could certainly use SQL to
calculate the start and end dates, but I suspect you'll find using a table
to be easier than a variety of procedures.

Bruce

On Thu, Nov 29, 2018 at 1:20 PM Hiebert, Chris <chris.hiebert@xxxxxxxxxxxxxx>
wrote:

"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
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com