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!
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.
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.
The documentation strongly recommends against doing so: Exercise caution when comparing local time and UTC time
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:
{
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:
Result:
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().
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue