How to convert datetime ISO 8601 to ObjectScript datetime

Question

Question

Kurro Lopez · Nov 4, 2021

#Caché

Hi all,

I have a process that recive a datetime in ISO 8601 format (YYYY-MM-DDThh:mm:ss+001) to %timestamp value.

Example: set myDatetime = "2021-11-04T11:10:00+0100"

I've triyed use $SYSTEM.SQL.CONVERT(myDatetime,"SQL_TIMESTAMP") but it doesn't work.

Any idea?

Regards,
Kurro Lopez

Product version: HealthShare 2017.2

$ZV: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U) Thu Apr 12 2018 10:02:23 EDT

Discussion (7)1

Log in or sign up to continue

Vitaliy Serdtsev · Nov 4, 2021

$zdth()

USER>w $zdth("2021-11-04T11:10:00+0100",3,5)
66052,40200

1 0

Julius Kavay · Nov 4, 2021

According to documentation, the tformat paramer 5 is ignored:

"Specify time in the form "hh:mm:ss+/-hh:mm" (24-hour clock). The time is specified as local time. The following optional suffix may be supplied, but is ignored: a plus (+) or minus (–) suffix followed by the offset of local time from Coordinated Universal Time (UTC). A minus sign (-hh:mm) indicates that the local time is earlier (westward) of the Greenwich meridian by the returned offset number of hours and minutes. A plus sign (+hh:mm) indicates that the local time is later (eastward) of the Greenwich meridian by the returned offset number of hours and minutes."

The same goes for the parameter values 6, 7 and 8

write $zdth("2021-11-04T11:10:00+0100",3,5)  --> 66052,40200
write $zdth("2021-11-04T11:10:00+0200",3,5)  --> 66052,40200
write $zdth("2021-11-04T11:10:00-0100",3,5)  --> 66052,40200

0 0

Vitaliy Serdtsev · Nov 19, 2021

It's okay, I already have a minus.
It looks like this is my last comment here.

UPD:

  w $$Iso8601ToTimeStamp("2021-10-22T08:00:00+0900"),!
  w $$Iso8601ToTimeStamp("2021-11-04T11:10:00+0100"),!
  w $$Iso8601ToTimeStamp("2021-11-04T11:10:00+0200"),!
  w $$Iso8601ToTimeStamp("2021-11-04T11:10:00-0140"),!

Iso8601ToTimeStamp(ts) {
  s r=##class(%TimeStamp).XSDToLogical($e(ts,1,22)_":"_$e(ts,*-1,*))
  w r," => "
  q $zdth(r,3)
 }

Output:

USER>do ^test
2021-10-21 23:00:00 => 66038,82800
2021-11-04 10:10:00 => 66052,36600
2021-11-04 09:10:00 => 66052,33000
2021-11-04 12:50:00 => 66052,46200

1 0

score 0 · Answer 1 · 2021-11-04T08:51:44-04:00

I know, that's why I gave a link to the documentation so that the author would do the rest of the work himself, since ISO 8601 has many forms.

Here is another variant of the ISO8601ToDateTime method.

score 0 · Answer 2 · 2021-11-04T08:56:43-04:00

Oh, one have to hover over the function name to see, that's a link! Maybe I have to blame my monitor because of the more grayish then bluish color.

score 0 · Answer 3 · 2021-11-04T11:40:35-04:00

Kurro Lopez · Nov 4, 2021

Thanks.. the 3,5 was the answer... now it's working !!!

0 0

score 0 · Answer 4 · 2021-11-04T08:36:41-04:00

Usually, I solve such problems (it's faster then searching for some funy SQL or other functions) by writing my own function/method/expression, depending on the current requirement.

ClassMethod TimeZoneToHorolog(tz)
{
   set t=$zdth(tz,3,5), t=t*86400+$p(t,",",2)+($e(tz,20,22)*60+$e(tz,23,24)*60)
   quit t\86400_","_(t#86400)
}

Assuming, tz contains a timezone formatted string like:

2021-11-04T11:10:00+0300