New post
Question
Conor Browne · Sep 19, 2017

Convert timestamp string to UTC time

#Caché #ObjectScript

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

0
0 3,089
Discussion (8)1
Log in or sign up to continue
Kurro Lopez · Sep 20, 2017

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 

2 0
Conor Browne · Sep 20, 2017

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

0 0
Fabian Haupt · Sep 20, 2017

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

0 0
Robert Cemper · Sep 20, 2017

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

1 0
Robert Cemper · Sep 20, 2017

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

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

1 0
Eduard Lebedyuk · Sep 20, 2017

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.

3 0
Vitaliy Serdtsev · Sep 21, 2017 
w "$ZTZ=",$ztz,!

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

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

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

Result (GMT+02:00, Chisinau):

USER>d ^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>d ^test
$ZTZ=-120
Winter time: 20160105125915 -> 20160105105915
Summer time: 20160705125915 -> 20160705105915
2 0