If you've looked behind the scenes of the Tag Historian, you've probably noticed the timestamps are not stored as standard SQL timestamps. They are stored in a variant of Unix Time, or the number of milliseconds since January 1, 1970 00:00:00. The time may come when you need to convert that timestamp to a more human-readable format. This article will quickly cover how to do it in MySQL, MSSQL, and PostgreSQL.
MySQL
It's pretty easy to deal with unix timestamp in MySQL because they have a built-in function for doing so. The FROM_UNIXTIME() function will take in a unix timestamp and spit out the current timestamp.
Usage: SELECT FROM_UNIXTIME(t_stamp/1000) FROM sqlt_data...
MSSQL
In Microsoft SQL Server, it's a little more verbose. We use the DATEADD() function to figure out the timestamp.
Usage: SELECT DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') FROM sqlt_data...
PostgreSQL
PostgreSQL uses the function to_to_timestamp() for the conversion. A second example is provided for situations where the timezone needs to given proper conversion.
Usage:
SELECT to_timestamp(t/stamp/1000) AS ts, * FROM sqlt_data_...
SELECT to_timestamp(t_stamp/1000) AT TIME ZONE 'Canada/Mountain' AS ts, FROM sqlt_data_...
Other databases will usually be similar to one of the above. If you've come across one that we haven't covered and would like it added to this article, please let us know in our forum
Comments
4 comments
For SQL Server, we can expand the example to retain milliseconds:
DATEADD(ms, t_stamp % 1000, DATEADD(s, t_stamp / 1000, '1970-01-01'))
For PostgreSQL...
Usage: SELECT to_timestamp(t_stamp/1000.0) AS ts, * FROM sqlt_data_...
It may also be useful to note that "standard" Unix Epoch time is the number of seconds since January 1, 1970 00:00:00 UTC. The Ignition variant uses milliseconds instead of seconds and that is why all the examples start by dividing the returned t_stamp value by 1000.
Reference: https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663
How do we accommodate for the time zone in the MSSQL option?
(MSSQL)Here is what I have found to get the time adjusted for time zone:
CONVERT(datetime, TODATETIMEOFFSET(DATEADD(s,t_stamp/1000, '1970-01-01), 0) AT TIME ZONE 'US Eastern Standard Time') AS Adjusted_Time
Please sign in to leave a comment.