Question
· 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!

Discussion (6)0
Log in or sign up to continue

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