New post
Question
Nicholas Chimera · Jun 21, 2018

Convert UTC to local time via SQL

#Caché

Hello,

I’m running  a query to drive a report.

 

I have a persistent class that includes this:

Property CreationTime As %TimeStamp [ InitialExpression = {$ZDateTime($ZTimeStamp, 3, 1, 2)} ];

 

So the above populates the column with UTC time.

 

Is there a way to convert to local time inside the SQL query?

 

 

Thanks!

0
0 3,180
Discussion (6)0
Log in or sign up to continue
Dmitry Maslennikov · Jun 21, 2018

This format does not contain information about timezone, so, you don't have any direct way how to convert it to the local time. You can use DATEADD function, to correct time, but you should use constant correction, or in another field.

SELECT DATEADD(hh, 3, CreationTime) FROM Table

Like, here I just add 3 hours.

0 0
Nicholas Chimera · Jun 22, 2018

The problem with that is the offset is not constant due to  DST.

So right now I am  -4:00 but two months ago I was -5:00 .

Thanks.

0 0
João Navarro · Feb 11, 2020

You may also do:

SELECT DATEADD('hh', DATEDIFF('hh', GETUTCDATE(), NOW()), CreationTime) FROM Table

This way datediff will calculate the difference from your timezone to UTC and use it.
 

0 0
Robert Cemper · Jun 21, 2018

You can also create your own conversion function like this:

Class SQL.ext [ Abstract ]
{
ClassMethod fromUTC(ts As %TimeStamp) As %TimeStamp [ SqlName = fromUTC, SqlProc ]
{
 quit $ZDT($ZDTH($ZDTH(ts,3),-3),3,1,2)
}
}

Then you simply use

 SELECT SQL.fromUTC(CreationTime) FROM Table

to get the conversion​​​​​​​

0 0
Vitaliy Serdtsev · Jun 22, 2018

Use %UTC instead of %TimeStamp

In this case, all conversions will be performed automatically.

Simple example:

Class dc.test Extends %Persistent [ Final ]
{

Property CreationTime As %TimeStamp [ InitialExpression = {$ZDateTime($ZTimeStamp, 3, 1, 2)} ];

Property utc As %UTC [ InitialExpression = {##class(%UTC).NowUTC()} ];

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test default values)
  zw ^dc.testD
  
  w !
  
  ; %SelectMode = DISPLAY
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(2),"select * from dc.test").%Display($c(9,9)) w !

  ; %SelectMode = ODBC
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(1),"select * from dc.test").%Display($c(9,9)) w !

  ; %SelectMode = LOGICAL
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(0),"select * from dc.test").%Display($c(9,9))
}
}

Result:

USER>d ##class(dc.test).Test()
^dc.testD=1
^dc.testD(1)=$lb("2018-06-22 14:58:11.61","2018-06-22 14:58:11.618") note that the database stores the same values
 
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 17:58:11.618
 
1 Rows(s) Affected
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 17:58:11.618
 
1 Rows(s) Affected
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 14:58:11.618
 
1 Rows(s) Affected

Similarly, you can play with the query in the Portal or any ODBC/JDBC client.

PS: don't forget about SQL Functions: %internal()/%external(), %odbcin()/%odbcout().

0 0