Lookout ODBC Data Transforms Wrong TimeStamp

[JTJams]

When using MS Access pass-thru SQL and the Lookout ODBC Driver & Data Transforms, the TimeStamp appears to be off by one full day when using Interval = 1, such as:

SELECT Traces.LocalTime, Traces."Starts{\\D00003588\LybrookStation\Drivers\TiVar@StsElect1On}", Traces."Stops{\\D00003588\LybrookStation\Drivers\TiVar@StsElect1On}",
Traces."ETM{\\D00003588\LybrookStation\Drivers\TiVar@StsElect1On}",
Traces."Starts{\\D00003588\LybrookStation\Drivers\TiVar@StsElect2On}", Traces."Stops{\\D00003588\LybrookStation\Drivers\TiVar@StsElect2On}",
Traces."ETM{\\D00003588\LybrookStation\Drivers\TiVar@StsElect2On}",
Traces."Starts{\\D00003588\LybrookStation\Drivers\TiVar@StsElect3On}", Traces."Stops{\\D00003588\LybrookStation\Drivers\TiVar@
StsElect3On}",
Traces."ETM{\\D00003588\LybrookStation\Drivers\TiVar@StsElect3On}"
FROM Traces
WHERE Traces.Interval="1" AND Traces.LocalTime>= "7/01"

For example, the query will return 6 starts for July 8th, but when I look up the data in a HyperTrend, the number of starts on the 8th is actually 3, and I see that there are 6 starts on the 7th, which reported only 2.

Any ideas?

[jdigiova]

Hello,

It appears that your condition for LocalTime does not include a time. By default the data will be returned for a time of 00:00. The interval is based on a day. Therefore if you want to sample the database and pull a value from the end of a day you need to specify a time in the LocalTime condition. Otherwise you will be sampling the data at the beginning of the day. For example, compare the following SQL queries and the results:

SELECT Traces."\\jdigio\Process1\Counter1", Traces."LocalTime",Traces."Interval"
FROM Traces Traces
WHERE (Traces.Interval="1" And Traces."LocalTime">="8/12")

returns

2004-08-12 00:00 1
8 2004-08-13 00:00 1
0 2004-08-14 00:00 1
2 2004-08-15 00:00 1

however the sql query

SELECT Traces."\\
jdigio\Process1\Counter1", Traces."LocalTime",Traces."Interval"
FROM Traces Traces
WHERE (Traces.Interval="1" And Traces."LocalTime">="8/12 23:59")

returns

8 2004-08-12 23:59 1
0 2004-08-13 23:59 1
2 2004-08-14 23:59 1

Notice by adding the time the data is shifted up and corresponds to data taken at 11:59 pm rather then 00:00 am. You can also change the interval condition to a fraction and sample multiple times a day.

Regards,
Joseph DiGiovanni
National Instruments

[JTJams]

Thanks for the clarification!

JTS