MSSQL datevalue = null

Ignition
1

Hi everyone,

I am trying to create an sql query that records the batchStart and batchEnd datevalue in the MSSQL database (triggered by the changing of the tag's intvalue), and use this period of time to calculate things like average process value, min, max etc. This is for a production report.

In the database, the datevalue column is returning only null values. I tried to use the t_stamp column, but upon further reading I've realised that this is the rowversion and not a replacement for the date/time. Is there a simple way to enable datevalues to be recorded in the database?

Cheers

2

Is this an entry by the Tag Historian?

I would be inteterested to know what value of t_stamp you're seeing that makes you think it is a row version and not a timestamp. Also, can you point to where you read this? Or do you mean that you did some reading of internal documentation?

For what it's worth the t_stamp column as inserted by the Tag Historian is a timestamp, in milliseconds from Unix Epoch (Jan 1, 1970). It can be converted to a more Human Readable date using the fromMillis() expression function or the system.date.fromMillis() scripting function.

1 Like
3

Thanks Irose,

I read it under one of the topics on this forum, but I may have misinterpreted.

This is entered by the tag historian into the MS SQL database. The t_stamp value looks like this: 1741086679216.

4

That's it in Unix timestamp. Add this: “SELECT DATEADD(s,t_stamp/1000,‘1970-01-01 00:00:00’)”

1 Like
5 
print system.date.fromMillis(1741086679216)

Output:

>>> 
Tue Mar 04 06:11:19 EST 2025
>>>

The t_stamp column is most definitely a timestamp.

1 Like
6

This is the tag historian, so I would hope they are not directly querying the database.

2 Likes
7

Try something like this. Once you have a DateandTime column in the format you are expecting, you can Query between specified datetimes, or backwards an hour, 24 hours, etc, pretty simply.

SELECT TOP (100000000) [Dateandtime],[tagid]
      ,[intvalue]
      ,[floatvalue]
      ,[stringvalue]
      ,[datevalue]
      ,[dataintegrity]
      
  FROM

  (
 SELECT CAST(DATEADD(SECOND, (t_stamp/1000) - 18000
 ,'1970/1/1') AS DateTime) DateandTime, tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity
from Ignition_Server.dbo.sqlth_1_data
) as src
1 Like
8

Concur. Use the system.tag.* functions to retrieve from the historian.

1 Like
9

For each of these, +1 for using native system functions. The Ignition functions will do the more complicated series of queries required to cross boundaries of data table partitions, aggregate values (averages, min, max, etc). System functions can also export to CSV and send an e-mail, if that is the method of reporting you prefer.

Should you decide that a manual query is the right option for you, here is a link to Ignition Database Table Reference.
Under the ' sqlt_data_X_X' section, you'll see that the 'datevalue' column has a note:

Holds the value of the tag if it is data type 3, NULL otherwise.

And, as others have identified, 't_stamp' with the note:

Unix Timestamp (milliseconds since epoch) for this value.

Heed the caution at the top of the page. Do not attempt to alter data within the tables.

1 Like