Re: UTC offset headaches

On Fri, 2017-04-21 at 21:40 -0400, Dan wrote:

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.

<snip>

The QWCCVTDT api has as the first optional parameter group the ability
to pass in *SYS, *UTC, *JOB, or a *TIMZON object as both the input date
format and the output date format.

I've not used it for time zone conversions (I've only ever used it for
time/date type conversions such as *DTS to *DMY shifts) but if I'm
reading the manual correctly then it should be possible to go from their
*UTC format as the optional input parm, then have the optional output
parm as *SYS (or a *TIMZON) format and it should convert it to the
correct system time.

Obviously you'd need to check that my interpretation of the manual is
correct, (and the number of subseconds might require some finangling
depending on how UTC is defined and/or input date type, as I say I've
not used time conversion) but it shouldn't take to long to mock up a
couple of known test dates/times and see if it converts correctly.
Obviously others may have more experience of this API. or possibly SQL
might have usable date/time conversion functions?

- Dan

Jon