Question
Nicholas Chimera · Jun 21, 2018

Convert UTC to local time via SQL

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!

00
0 6 1,494 2

Replies

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.

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.

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.
 

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​​​​​​​

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()
{
  ..%KillExtent()
  
  &sql(insert into dc.test default values)
  zw ^dc.testD
  
  !
  
  ; %SelectMode = DISPLAY
  ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(2),"select * from dc.test").%Display($c(9,9)) !

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

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

Result:

USER>##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().