Timestamp tag handling in PI-OLEDB

Discussion created by IanGore on Apr 21, 2010
Latest reply on Apr 22, 2010 by spilon

Thought you might be interested in a little "feature" regarding the handling of tags of pttype=timestamp in the PI-OLEDB interface which we found when upgrading the PI-OLEDB provider. 


In PI-OLEDB version 3.1.x, if you queried the value of a timestamp tag you got the value in UTC seconds - that is seconds since 01-JAN-1970.  In SQL Server you could convert that to a datetime value doing something like DATEADD(ss, <tag value>, '01-JAN-1970') (would also need timezone handling to do it right).


In PI-OLEDB version 3.2.x, when you query the value you get a number which appears to be the number of days since 01-JAN-1900.  The value returned matches the value that is returned in Excel by datalink or when you format a timestamp as a number.   However, if you then try to cast the value to a datetime (eg CAST(<tag value> AS datetime)) then you get a timestamp that is two days out.


In Excel a datetime is stored as a serial time being the number of days since 01-JAN-1900.
In SQL a datetime is stored as a serial time being the number of days since 01-JAN-1900.


So why the two day difference?.  There are two parts to it…


1) In Excel '01-JAN-1900' is day 1 whereas in SQL '01-JAN-1900' is day 0.


2) There is a deliberate 'bug' in Excel whereby 1900 is treated as a leap year and has an extra day.  Apparently (isn’t Google wonderful) this was originally done to make Excel compatible with Lotus 123 which had the 'real' bug in it.


So if you want to convert the value in a PI timestamp tag to an SQL datetime you have to subtract 2 to get it right.


Note that in both versions of the PI-OLEDB provider the timestamp of the value is handled correctly.  So the provider "knows" how the date is converted for the timestamp but not how the date is converted for the value.