Re: UTC offset headaches

Hi Dan,

Actually IBM provides UTC functionality in SQL, but it is a bit hidden. I'm
not sure in which direction you experience the problems, incoming or
outgoing, but here are some SQL examples.

SELECT x."MyPureUtcStamp"
FROM
XMLTABLE('fn:adjust-dateTime-to-timezone(xs:dateTime("2016-12-05T08:00:00-07:00"))'

COLUMNS "MyPureUtcStamp" TIMESTAMP PATH '.') AS x ;


VALUES
CASE
WHEN HOUR(CURRENT_TIMEZONE) = -4 THEN
(
SELECT *
FROM
XMLTABLE('fn:adjust-dateTime-to-timezone(xs:dateTime("2016-12-05T08:00:00-07:00"),


xs:dayTimeDuration("-PT4H"))') AS x
)
WHEN HOUR(CURRENT_TIMEZONE) = -5 THEN
(
SELECT *
FROM
XMLTABLE('fn:adjust-dateTime-to-timezone(xs:dateTime("2016-12-05T08:00:00-07:00"),


xs:dayTimeDuration("-PT5H"))') AS x
)
END ;


For building an outgoing timestamp string (ISO 8601 format for XML) I use
SQL's CURRENT TIMEZONE special register. It will always reflect your
systems distance to UTC with DST in mind.

VALUES REPLACE(TO_CHAR(CURRENT_TIMESTAMP(7), 'YYYY-MM-DD/HH24:MI:SS.FF7'),
'/', 'T')
|| CASE WHEN CURRENT_TIMEZONE = -40000 THEN '-04:00' ELSE '-05:00'
END ;

Hope that might help you.

Best regards,
-Arco





2017-04-22 3:40 GMT+02:00 Dan <dan27649@xxxxxxxxx>:

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD