×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
Hi Dan,
You can use the QWCCVTDT API to convert to and from UTC - using the respective time zone names to locate the correct offset to be factored into the conversion. More details as well as code can be found in the following articles:
Michael Sansoterra - Present Timestamps in the Local Time Zone
https://www.itjungle.com/2006/05/24/fhg052406-story02/
APIs by Example: Date and Time APIs
http://iprodeveloper.com/print/rpg-programming/apis-example-date-and-time-apis
Cheers,
Carsten
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: 22. april 2017 03:41
To: Midrange Systems Technical Discussion
Subject: UTC offset headaches
We are getting ready to start trading data with a partner. This will be the first time for us in that the partner is located in a different time zone and the data we are trading includes timestamps. Our partner sends us timestamps in a UTC format, such as "2016-12-05T08:00:00-07:00". (FWIW, I believe they are an Oracle shop.) As I understand it, to correctly convert it to our format, I would first need to add 7 hours to the 08:00:00 time, then subtract whatever our local UTC offset was *on the date shown in the timestamp*. So, if we were using EST on 2016-12-05, I would have to subtract the offset in effect on that date, which is 5 hours. Ergo, that timestamp value should be converted on our system to "2016-12-05-10.00.00.000000". Can anyone confirm that I have this right?
(I understand I would have to consider midnight boundaries and adjust the date as necessary.) Due to the nature of our business, we need only be concerned with time zones in the United States.
The headache I am experiencing is that when I need to convert one of our timestamps to a UTC timestamp, I have to consider the date in the timestamp value to determine which offset to use. If the date is in Daylight Saving Time, I need to convert using an offset of 4 hours, otherwise 5 hours.
This looks to be a total PITA, and I think I would need to create a table with the record defined with a date range and the UTC offset in effect at the time. I was hoping to be able to do this conversion in straight SQL, but now it appears I need to involve a lookup table in a function.
Makes me seriously wonder why IBM did not build in a UTC offset component into the timestamp data type.
midrange.com
