Convert UTC to local time via SQL

You can also create your own conversion function like this:

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