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?
I found the solution that worked for me. I used the CONVERT function - CONVERT(datetime, DATEADD(second, t_stamp/1000, '19700101'))
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.)