Sign in to follow this  
jbotas

Alarm Log Timestamp

Recommended Posts

I have a few alarms logging to a SQL DATABASE.

 

The timestamps of the log is not equal to the timestamps of the alarms, always 2 days and 30 ~70 milliseconds more. The 2 days is about the initial time reference, so no problem here, i just subtract 2 days. But the 30 ~ 70 milliseconds i don't know why the difference.

 

If i change the log type to a file the time stamp is the same as the alarms, even the milliseconds.

Share this post


Link to post
Share on other sites

There is a bug in MS SQL in their date/time implementation.  I don't know what it is about, but its been there for a long time.  If we send time stamps using what we call Excel time, decimal days since 1900, for some reason MS SQL is off by two days (never noticed the millisecond part).  Given that if you send the same number to Excel another Microsoft product, even through ODBC, it comes out right, I cannot understand why MSSQL would have the issue, but there it is.  We have a variable for logging sets called TimeOffset that allows you to shift the time stamps appropriately for this Microsoft bug, but nothing for alarms.  My first recommendation would be to simply use a different database, like MySQL.  Otherwise, post a request in the feature request forum.

Share this post


Link to post
Share on other sites

I don't have a problem with the 2 days offset. My problem is that the TimeStamp of the log is not equal to the time it fired in the milliseconds.

 

If i have multiple alarms appear at the same time when i run "Alarm.CheckAlarms()" the Timefired is diferent in every alarm by 1 or 2 milliseconds.

But in the log, all alarms have the same timestamp but with more 30 ~ 50 miliseconds.

EX:

In the Alarms

ALM00.Timefired - 2015-02-13 12:00:00.100

ALM01.Timefired - 2015-02-13 12:00:00.102

ALM02.Timefired - 2015-02-13 12:00:00.103

ALM03.Timefired - 2015-02-13 12:00:00.105

 

In the Log

ALM00.TheTime- 2015-02-15 12:00:00.147

ALM01.TheTime- 2015-02-15 12:00:00.147

ALM02.TheTime- 2015-02-15 12:00:00.147

ALM03.TheTime- 2015-02-15 12:00:00.147

 

The two days diference is not a problem with me. As i said i can remove this 2 days shift when i process the data. But the Millisencods part is not correct with what the alarms say.

For me the time in the log must be the same as the one that appears in the alarms.

Share this post


Link to post
Share on other sites

Today i did some testing about this problem.

 

I used a MDB (Acess) database and the problem with the milliseconds is present.

Share this post


Link to post
Share on other sites

The problem is the precision that Access and MSSQL use to store the date/time stamp.  While DAQFactory is writing the value as a double precision float, somewhere, either in the ODBC driver or the database itself, that is getting truncated, possibly to a single precision float.  These databases can store dates going from 100A.D to 9999 A.D and as such precision gets lost at the low end.  See this page:

 

https://support.office.com/en-ie/article/Insert-create-or-delete-a-field-that-stores-date-values-8a6900a4-72d8-4dbf-be09-f0aec8a86ed1?ui=en-US&rs=en-IE&ad=IE

 

My recommendation is to let DAQFactory create the table, then change the field type of "TheTime" from DATE to DOUBLE, or you might even try DATETIME.  I'm not totally up on MSSQL data types as I typically work with MySQL.

Share this post


Link to post
Share on other sites

I tested this with mysql and firebird.

The problem exists in every Database with ODBC connection.

The real problem is even if i hide the milliseconds, sometimes the alarms enter near the second. The logged second and the alarm time is in a different second.

Share this post


Link to post
Share on other sites

I'm using version 5.91 build 2203.

 

Another thing that i saw is that the value of the time in the databases is always caped to 6 decimal digits. ex: 1234.1234560000000

 

I made a script convert the time fired of one alarm with the to excel type (hours since.%day -> ex:1234.1234567890123456), caped to 6 decimal digits (1234.12345600000000), and then returned to epoch time (seconds since.milliseconds). The time now is only different in 1 to 3 milliseconds (probably because of 64 bit float scope versus the conversion made by the databases).

 

Before i did this transformation it was 30 to 40 milliseconds different.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this