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 and MSSQL.
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...
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
Understanding Tag Historian Timestamps
Comments
2 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
Please sign in to leave a comment.