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.

I can't be the first one to have to deal with this, so I'm wondering if
those who have BT,DT can shed some wisdom.

- Dan

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.