# Understanding Tag Historian Timestamps in PostgreSQL **URL:** \ **Category:** Ignition\ **Created:** [August 31, 2022, 11:42pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663 "2022-08-31T23:42:53Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [August 31, 2022, 11:42pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/1 "2022-08-31T23:42:53Z")
@dave.fogle has produced an article titled [Understanding Tag Historian Timestamps](https://support.inductiveautomation.com/hc/en-us/articles/360047136252-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](https://support.inductiveautomation.com/hc/en-us/articles/360047136252-Understanding-Tag-Historian-Timestamps). 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;` **Useful references:** - [https://support.inductiveautomation.com/hc/en-us/articles/360047136252-Understanding-Tag-Historian-Timestamps](https://support.inductiveautomation.com/hc/en-us/articles/360047136252-Understanding-Tag-Historian-Timestamps) - [Ignition Database Table Reference - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/Ignition+Database+Table+Reference#IgnitionDatabaseTableReference-sqlt_data_X_X) - [PostgreSQL: Documentation: 12: 9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT) - [PostgreSQL: Documentation: 12: 9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-TABLE) ---
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [September 1, 2022, 3:36pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/2 "2022-09-01T15:36:37Z")
Here is an example of a query for pulling tag history from a PostgreSQL database. With a specific tagpath pattern and time range.. ```sql SELECT to_timestamp(t_stamp/1000.0) AT TIME ZONE 'UTC' as UTC , to_timestamp(t_stamp/1000.0) AT TIME ZONE 'US/Central' as US_Central , A.t_stamp , A.tagid , A.floatvalue , A.dataintegrity --, A.* FROM sqlt_data_1_20220729 A WHERE tagid in (select id from sqlth_te where tagpath like '%power/%pm-04-04%/energy') AND t_stamp BETWEEN (EXTRACT(EPOCH FROM TIMESTAMP '2022-08-02 00:00:00.000')*1000) AND (EXTRACT(EPOCH FROM TIMESTAMP '2022-08-03 00:00:00.000')*1000) ``` - Update 2023-11-10: - Changed `to_timestamp(t_stamp/1000)` to `to_timestamp(t_stamp/1000.0)`. **Results (using Database Query Browser):** ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/3/e38ee6cd0395eb15af62e4c5d7da736e5f5d1999.png) ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 1, 2022, 3:59pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/3 "2022-09-01T15:59:21Z")
Just in case you aren’t aware, you can just use a system function and get tag history, probably quite a bit easier than what you are doing. [https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory) ---
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [September 1, 2022, 4:52pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/4 "2022-09-01T16:52:39Z")
Thanks, I’m aware of those Ignition functions, but I had a need (desire) to examine tag history directly for investigating and troubleshooting apparent oddities that were being returned using the built-in [system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory) and [system.tag.queryTagCalculations](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagCalculations) Ignition system functions and charting components. It seems built-in Ignition features (charts etc.) default to returning a lot of interpolated data and it’s not always apparent how to reduce to as-stored values only (or at least it is easy to overlook). I’m a little untrusting by nature so it is comforting to be able to examine the source data directly. That is easy enough to do by using the [Database Query Browser](https://docs.inductiveautomation.com/display/DOC81/Database+Query+Browser) in designer (if one is familiar with SQL) except for the interpretation of the atypical timestamps (the topic of this thread). I’m not actually suggesting that direct SQL queries for tag history be used for for features exposed to the end user, I would avoid that, but I think it is valuable knowledge for investigation/troubleshooting. Note that my example does not dynamically handle tag history across multiple tag history partition tables. Though I’d be interested if someone has developed an parameterized example of how to achieve that with PostgreSQL so I can just feed in a date range and tag path and get the results. ---
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [September 1, 2022, 5:54pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/5 "2022-09-01T17:54:44Z")
For comparison, here is an example showing how to query and review similar data using the Script Console (Designer) and the [system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory) function. ``` myTagPaths = ['[default]Station Power/Station 4/PM-04-04/Energy' ] myColumnNames = ['PM-04-04'] myEndDateTime = system.date.parse("2022-08-03 00:00:00", "yyyy-MM-dd HH:mm:ss") myStartDateTime = system.date.addHours(myEndDateTime, -24) print myEndDateTime print myStartDateTime #====================================================================== #====================================================================== def printDataSet(datasetIn): # Get Row and Column counts nrows = datasetIn.getRowCount() ncols = datasetIn.getColumnCount() # Get max length of row labels rowLen = len(max(['row']+[unicode(i) for i in range(nrows)], key=len)) # Get column names colNames = datasetIn.getColumnNames() # initialize lists to process columns headerList = [] colData = [] maxLen = [] # Process columns for i, col in zip(range(ncols), colNames): # Get column as list, converting all elemenst to unicode strings colIn = ([unicode(element) for element in list(datasetIn.getColumnAsList(i))]) # Get max lentgh of the column, including the column name maxLen = len(max([col]+colIn, key=len)) # Append data as left justified strings. # ljust() will automatically pad to the max string length colData.append([element.ljust(maxLen) for element in colIn]) # Append column name, also using the max string length headerList.append(col.ljust(maxLen)) # Concatenate the header string and print it. headerString= 'row'.ljust(rowLen) + ' | ' + ' | '.join(headerList) print headerString # Print a bunch of dashes the same length as the header string print'-' * len(headerString) # Print the rows for row in enumerate(zip(*colData)): print unicode(row[0]).ljust(rowLen) + ' | ' + ' | '.join(row[1]) #====================================================================== #====================================================================== data = system.tag.queryTagHistory( paths=myTagPaths , startDate=myStartDateTime , endDate=myEndDateTime # , returnSize = 0 # , aggregationMode="LastValue" # , returnFormat , columnNames=myColumnNames # , intervalHours # , intervalMinutes # , rangeHours # , rangeMinutes # , aggregationModes # , includeBoundingValues=1 # , validateSCExec , noInterpolation=1 # prevent the return of rows that are interpolated # , ignoreBadQuality , timeout = 10000 # , intervalSeconds # , rangeSeconds ) printDataSet(data) ``` **Results:** ``` >>> Wed Aug 03 00:00:00 UTC 2022 Tue Aug 02 00:00:00 UTC 2022 row | t_stamp | PM-04-04 -------------------------------------------------- 0 | Tue Aug 02 00:06:17 UTC 2022 | 17.8490009308 1 | Tue Aug 02 01:06:23 UTC 2022 | 18.2940006256 2 | Tue Aug 02 02:06:29 UTC 2022 | 18.7299995422 3 | Tue Aug 02 03:06:33 UTC 2022 | 19.1550006866 4 | Tue Aug 02 04:06:37 UTC 2022 | 19.577999115 5 | Tue Aug 02 05:06:43 UTC 2022 | 19.9960002899 6 | Tue Aug 02 06:06:49 UTC 2022 | 20.4130001068 7 | Tue Aug 02 07:06:55 UTC 2022 | 20.8269996643 8 | Tue Aug 02 08:07:00 UTC 2022 | 21.2390003204 9 | Tue Aug 02 09:07:06 UTC 2022 | 21.6520004272 10 | Tue Aug 02 10:07:13 UTC 2022 | 22.0620002747 ... ``` To me this actually seems quite a bit less versatile than querying the database directly but it does have the advantage of transparently handling the history data partitioned across database tables. There is probably a way, but it is not immediately apparent, how one might use localized time in the query and show localized time in the returned data. The `printDataSet` function was lifted from a [forum post](https://forum.inductiveautomation.com/t/view-dataset-from-script-console/36704/4) by @JordanCClark, (thank you). ---
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [November 10, 2023, 8:20pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/6 "2023-11-10T20:20:05Z")
Somewhat updated query that I now prefer as it allows access to fields in the `sqlth_te` table to include in the results. ```sql SELECT to_timestamp(t_stamp/1000.0) AT TIME ZONE 'UTC' AS UTC , to_timestamp(t_stamp/1000.0) AT TIME ZONE 'US/Central' AS US_Central , to_timestamp(t_stamp/1000.0) AS ts --, tags.* , tags.tagpath --, data.* -- , data.tagid , data.intvalue , data.floatvalue , data.stringvalue , data.dataintegrity -- , data.t_stamp FROM sqlt_data_1_20231103 data JOIN sqlth_te tags ON tags.id = data.tagid WHERE tags.tagpath = LOWER('BoP Power/PM-10-00/FREQ') AND t_stamp BETWEEN (EXTRACT(EPOCH FROM TIMESTAMP '2023-11-04 00:00:00.000')*1000) AND (EXTRACT(EPOCH FROM TIMESTAMP '2023-11-04 01:00:00.000')*1000) ``` - Update 2023-11-10: - Changed `to_timestamp(t_stamp/1000)` to `to_timestamp(t_stamp/1000.0)`. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 10, 2023, 8:29pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/7 "2023-11-10T20:29:30Z")
If you divide by 1000.0 instead of 1000, you should retain the milliseconds in the `timestamptz` output. Note that to\_timestamp() is defined to return a timestamptz, so it is fundamentally UTC-based, and will be transferred to a `java.util.Date` with that UTC point-in-time intact. Using the `AT TIME ZONE` clause converts to non-UTC-based `timestamp`, that is unreliable when different database clients use different connection time zones. You should only use that clause when it is nested inside a string conversion, as allowing `timestamp` to travel through JDBC exposes that unreliability. ---
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [November 10, 2023, 9:06pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/8 "2023-11-10T21:06:16Z")
> [@pturmel](#): > > If you divide by 1000.0 instead of 1000, you should retain the milliseconds Thanks for the divide by 1000.0 suggestion. I failed to notice that my results were all missing milliseconds. I'll update my examples above. Regarding the use of `AT TIME ZONE` I added that because at one point I had some uncertainty about what timezone was being represented in the output. Even now when I use just `to_timestamp(t_stamp/1000.0)` in designer's database browser it gives me time in my gateway timezone (not UTC). I have just been considering the string interpretation. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 10, 2023, 9:37pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/9 "2023-11-10T21:37:52Z")
> [@tyler.bennett](#): > > it gives me time in my gateway timezone (not UTC). No, it is **printing** the time in the gateway timezone, but the underlying java object is timezoneless. I recommend you read this topic about datetime objects in Ignition in general: > [@System.date.now Timezone](https://forum.inductiveautomation.com/t/system-date-now-timezone/72478/8): > > The java.util.Date() object returned from any of Ignition's date and time functions, whether expression or script, Has No TimeZone! It is always UTC milliseconds. It is converted to a zoned format when converted to string. So, if converted to a string in the gateway, it will use the gateway timezone. If in Perspective, in the browser, it will use Perspective's client timezone. If you convert to string yourself, in a script or expression in Perspective, since those run in the gateway, you w… Then consider that PostgreSQL uses the **same technique** for its `timestamp with time zone` datatype (aka `timestamptz`), except that the underlying storage format is UTC **microseconds** instead of **milliseconds** (and a non-epoch zero point). So, `timestamptz` in PostgreSQL is fundamentally _timezoneless_, and is only shown with a timezone when converted to a string in an execution context that has a time zone. Same when parsing a string into a timestamp--uses the execution context's timezone, then stores/computes with the UTC microseconds. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 10, 2023, 9:45pm UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/10 "2023-11-10T21:45:42Z")
Further note: part of the reason IA uses 64-bit UTC microseconds for its `t_stamp` columns in the historian is that it _ **obviously** _ cannot be misinterpreted ("bungled", "warped", "fumbled", et cetera) by some stupid database, and 64-bit integers are supported data types in all usable databases. (Date/time data types are a true cluster-[expletive] across the various SQL brands--only slowly getting better.) ---
**Author:** ![tyler.bennett](https://avatars.discourse-cdn.com/v4/letter/t/f04885/32.png) [@tyler.bennett](https://forum.inductiveautomation.com/u/tyler.bennett)\ **Post date:** [November 11, 2023, 12:00am UTC](https://forum.inductiveautomation.com/t/understanding-tag-historian-timestamps-in-postgresql/63663/11 "2023-11-11T00:00:53Z")
> [@pturmel](#): > > No, it is **printing** the time in the gateway timezone, but the underlying java object is timezoneless. I understand that, as stated I was only using the text output (printed string interpretation). > [@tyler.bennett](#): > > I have just been considering the string interpretation. I simply used `AT TIME ZONE` to force the string representing for the specific time-zone(s) I desired, then I had no doubts as to what timezone the default string representation was associated with. I understand your points require consideration if when one might be feeding the query results into variables/objects for further processing and not just looking at the text output. However in that case I suggest one use Ignition's built-in system functions. [https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory)