Written by

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!

Discussion (6)0

Add reply

Comments

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 to Dmitry Maslennikov

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

Vitaliy Serdtsev Jun 22, 2018 to Dmitry Maslennikov

The documentation strongly recommends against doing so: Exercise caution when comparing local time and UTC time

0 0

João Navarro Feb 11, 2020 to Dmitry Maslennikov

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 ]
{
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">CreationTime </FONT><FONT COLOR="#000080">As %TimeStamp </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">InitialExpression </FONT><FONT COLOR="#000000">= {</FONT><FONT COLOR="#0000ff">$ZDateTime</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$ZTimeStamp</FONT><FONT COLOR="#000000">, 3, 1, 2)} ];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">utc </FONT><FONT COLOR="#000080">As %UTC </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">InitialExpression </FONT><FONT COLOR="#000000">= {</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%UTC</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">NowUTC</FONT><FONT COLOR="#000000">()} ];
</FONT><FONT COLOR="#000080">/// d ##class(dc.test).Test()
ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test </FONT><FONT COLOR="#000080">default values</FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^dc.testD
  
  </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!
  
  </FONT><FONT COLOR="#008000">; %SelectMode = DISPLAY
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">(2),</FONT><FONT COLOR="#008000">"select * from dc.test"</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(9,9)) </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!
  </FONT><FONT COLOR="#008000">; %SelectMode = ODBC
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">(1),</FONT><FONT COLOR="#008000">"select * from dc.test"</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(9,9)) </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!
  </FONT><FONT COLOR="#008000">; %SelectMode = LOGICAL
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">(0),</FONT><FONT COLOR="#008000">"select * from dc.test"</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(9,9))
}
}</FONT>

Result:

USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT>
^dc.testD=1
^dc.testD(1)=$lb("2018-06-22 14:58:11.61","2018-06-22 14:58:11.618") <FONT COLOR="red">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