Query Database based on date instead of t_stamp in Ignition Tag Historian database

Ignition
1

Hello, I wanted to write a sql query (Microsoft Sql Server) against the Ignition Tag Historian database and Table sqlt_data_X_2023_XX. The table has the tag values and t_stamp (which is milliseconds). How can I query based on date instead of the t_stamp?

2

I found the solution that worked for me. I used the CONVERT function - CONVERT(datetime, DATEADD(second, t_stamp/1000, '19700101'))

3

Just make sure that your search is searching in milliseconds and not converting each t_stamp to a date as this would slow down the query a lot.

SELECT t_stamp, tag
FROM tablename
WHERE convertToDate(t_stamp) >= someDate

The above query has to convert every t_stamp before the compare.

A better way is to compare t_stamp with the millisecond value as this only has to do one conversion.

SELECT t_stamp, tag
FROM tablename
WHERE t_stamp >= toMIllisecond(someDateValue)

(The queries above are very rough pseudo-code just to give the idea.)

1 Like
4

Thank you