Question
Conor Browne · Sep 19, 2017

Convert timestamp string to UTC time

Hoping someone can help.

I need to convert a timestamp from the following format (YYYYMMDDhhmmss e.g. 20160105125915) to UTC time in the same format.

I presume I need to convert it to the system format ( like $HOROLOG ) and then use something like ConvertTimeStampToHorolog or perhaps LocalWithZTIMEZONEtoUTC but not sure of the syntax.

Thanks,

Conor


 

00
1 0 8 2,676
Log in or sign up to continue

Hi Conor.

If you try to convert directly this datetime to a $HOROLOG format, you'll have problems :)

Date is a valid format, however the time needs to convert a valid format, so modify the variable to convert a valid datetime format.

set myTimeStamp="20160105125915"

set myTimeStampValid=$EXTRACT(fecha,1,4)_"-"_$EXTRACT(fecha,5,6)_"-"_$EXTRACT(fecha,7,8)_" "_$EXTRACT(fecha,9,10)_":"_$EXTRACT(fecha,11,12)_":"_$EXTRACT(fecha,13,14)

Then you have a datetime in format YYY-mm-dd HH:MM:ss

Now, you need to convert this variable to $HOROLOG format

set myTimeStampHorolog=$ZDATETIMEH(myTimeStampValid,3,1)

now, convert to UTC using your local time, if you know what is the UTC difference, apply directly.

Example: I'm in Spain, so is CET (UTC+1), however in summer (CEST) is UTC+2

If you use the method ##class(%SYSTEM.Util).LocalWithZTIMEZONEtoUTC you'll have the UTC as zone time, but your Ensemble doesn't know if it's Summer o Winter time.

if is Summer time, you need to reduce an hour your local time

set myTimeStampUTC=##class(%SYSTEM.Util).LocalWithZTIMEZONEtoUTC(myTimeStampHorolog)
;; IF SUMMER TIME, REDUCE ONE HOUR
set myTimeStampUTC=$ZDATETIMEH($SYSTEM.SQL.DATEADD("hour",-1,myTimeStampUTC),3,1)

if you want to compare both datetimes (now, I'm in CEST, so I'm in UTC+2)

write "My local time:"_$ZDATETIME(myTimeStampHorolog,3,1)
write "UTC time:"_$ZDATETIME(myTimeStampUTC,3,1)

The result is:

My local timestamp:2016-01-05 12:59:15

UTC timestamp:2016-01-05 10:59:15

Now, you have the same problem, you have a time separated by ":", so only need to revert the conversion

set myUTC=$ZDATE(myTimeStampUTC,8)_$REPLACE($ZTIME($PIECE(myTimeStampUTC,",",*),1),":","")

and compare:

write myTimeStamp

write myUTC

Check the info about theses methods:

$ZDATETIMEH

$ZDATETIME

$EXTRACT

##class(%SYSTEM.Util).LocalWithZTIMEZONEtoUTC

I hope I have solved your problem

Best regards,

Francisco Lopez

P.S. Don't forget check the answer if it's the correct answer.

P.P.S. [EDITED] According to Eduard Lebedyuk (see below), you can replace

set myTimeStampValid=$EXTRACT(fecha,1,4)_"-"_$EXTRACT(fecha,5,6)_"-"_$EXTRACT(fecha,7,8)_" "_$EXTRACT(fecha,9,10)_":"_$EXTRACT(fecha,11,12)_":"_$EXTRACT(fecha,13,14)

to

set myTimeStampValid=$SYSTEM.SQL.TOTIMESTAMP(myTimeStamp,"YYYYMMDDHHMISS")

Thanks Mr. Lebedyuk 

Thank you so much Francisco, I accepted your answer. This would have taken me hours to figure out!

The time taken to explain is much appreciated,

Conor

Funny how you say 'unreadable' about a more compact layout, yet you are using short commands;)

like this:

set ts=20160105125915

 ;; convert to $H
set th=$zdth($e(ts,1,8)_" "_$e(ts,9,10)_":"_$e(ts,11,12)_":"_(ts#100),8)

 ;; convert to UTC
set tu=$ZDTH(th,-3)

 ;; show $ZTS format (on UTC+0200 Vienna,Austria)
write $ZTD(tu,3)

2016-01-05 13:59:15

You can of course do it in a single unreadable long cascaded function chain.

HTH

yeah,
that's for slow typing senior writer's without  autocomplete. laugh

BTW. wrapping of this editor is sometime more than disturbing.

To convert string of any format into $horolog, use TO_DATE function:

w $SYSTEM.SQL.TODATE("20160105125915","YYYYMMDD")
>63922

To convert string of any format to timestamp use TO_TIMESTAMP function:

w $SYSTEM.SQL.TOTIMESTAMP("20160105125915","YYYYMMDDHHMISS")
>2016-01-05 12:59:15

These functions are available in Caché ObjectScript and SQL.

"$ZTZ=",$ztz,!

#define 2utc(%t) $tr($zdt($zdt($zdth($system.SQL.TOTIMESTAMP(%t,"YYYYMMDDHHMISS"),3),-3),8,1)," :")

s t="20160105125915"
"Winter time: ",t," -> ",$$$2utc(t),!

t="20160705125915"
"Summer time: ",t," -> ",$$$2utc(t)

Result (GMT+02:00, Chisinau):

USER>^test
$ZTZ=-120
Winter time: 20160105125915 -> 20160105105915
Summer time: 20160705125915 -> 20160705095915

Note:

  • Takes into account summer/winter time
  • Is taken into account to option of OS for automatic daylight saving time

For example, if in the OS to disable automatic daylight saving time, the result will be different:

USER>^test
$ZTZ=-120
Winter time: 20160105125915 -> 20160105105915
Summer time: 20160705125915 -> 20160705105915