There is a neat file that comes with DB2 WebQuery called DATE_CONV - it's in their sample data library. One row for each day from 01-01-1900 to 12-31-2030.
It has all kinds of info about that specific date.. you can join to this file by nearly every date type you can think of. Some of the fields need populated (like your companies holidays, etc.)... add your own field for the offset if you like, then update the UTC offset for your locale using SQL (one time) and you're done.
Here are the fields:
Date (date format)
JDE Julian Date (CYYDDD decimal)
Date (MMDDYYYY packed decimal)
Date (MMDDYYYY zoned decimal)
Date (MMDDYYYY character)
Date (YYYYMMDD packed decimal)
Date (YYYYMMDD zoned decimal)
Date (YYYYMMDD character)
Date (MMDDYY packed decimal)
Date (MMDDYY zoned decimal)
Date (MMDDYY character)
Date (YYMMDD packed decimal)
Date (YYMMDD zoned decimal)
Date (YYMMDD character)
Century (2 characters)
Year (2 characters)
Month (2 characters)
Day (2 characters)
Year (4 digits)
Day of week (1-7)
Day of week (1-7)
Day of year (1-366)
Week of year (1-52)
Week of year (1-53)
Quarter of year (1-4)
Century (2 digits)
Year (2 digits)
Month (2 digits)
Day (2 digits)
Century, Year, Month CCYYMM (6 digits)
Day Name (Monday,etc.)
Quarter name (2008Q1)
Weekend Flag (Y or N)
Holiday (Y or N)
Day Before Holiday (Y or N)
Day AfterHoliday (Y or N)
Full Moon (Y or N)
Season (Spring, Summer, Autumn, Winter)
Fiscal year (4 digits)
Fiscal quarter (1-4)
Month name (January, etc)
Month abbreviation (Jan, Feb, etc)
Date in Julian format
CYYMMDD packed C = 0 for 1900 & C = 1 for
Date in Excel format
Week starting date (the prior Saturday)
Week ending date (the next Friday)
Same day last year
Current Day (Y/N)
Current Week (Y/N)
Current Month (Y/N)
Current Quarter (Y/N)
Current Year (Y/N)
Current Year to Date (Y/N)
Current Day Last Year (Y/N)
Current Week Last Year (Y/N)
Current Month Last Year (Y/N)
Current Quarter Last Year (Y/N)
Current Year Last Year (Y/N)
Current Year To Date Last Year (Y/N)
Previous Day (Y/N)
Previous Week (Y/N)
Previous Month (Y/N)
Previous Quarter (Y/N)
Previous Year (Y/N)
Previous fiscal year (Y/N)
Current fiscal year (Y/N)
Previous fiscal year to date (Y/N)
Current fiscal year to date (Y/N)
Nth Day of the Week of the month
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bruce Vining
Sent: Thursday, November 29, 2018 3:37 PM
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Subject: 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
--
Thanks and Regards,
Bruce
931-505-1915
--
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
midrange.com
