@dave.fogle has produced an article titled Understanding Tag Historian Timestamps which is beneficial in understanding timestamps returned when querying tag history tables directly and how to convert them to string format timestamps.
However that article does not provide a specific example for PostgreSQL using the to_timestamp() function. As I have had to rediscover this a few times now I thought I’d make a post so a can find it faster next time (and it may be useful to others).
PostgreSQL examples for converting Ignition variant of Unix Epoch time to string format timestamps.
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_...
Usually I forget that I need to divide by 1000. 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 before using the built-in Unix Epoch conversion functions (where available).
@dave.fogle please consider adding this to the next version of your article. Please also consider adding examples for converting from string timestamps to Ignition’s variant of Unix Epoch time (milliseconds since January 1st, 1970 at UTC).
Here are PostgreSQL examples (untested) for converting string format timestamps to milliseconds since January 1st, 1970. Note the multiplication by 1000 to switch from seconds to milliseconds. there is also caution needed to confirm you are dealing properly with timezone conversion.
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08')*1000;
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2001-02-16 20:38:40.12-08')*1000;
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12')*1000;