runtime report from citadel

[Mike@DTSI]

Been a while, but here i am again.
I am trying to retrieve the total time a point was active using SQL and Citadel.

Example 1 SQL:
SELECT LocalTime,"ETM{\\Mikedesktop\Server\PumpC\BC}" FROM TRACES WHERE LocalTime >= "2006-05-23" and LocalTime <= "2006-05-23" and Interval = "1"
0 record(s)

Example 2 SQL:
SELECT LocalTime,"ETM{\\Mikedesktop\Server\PumpC\BC}" FROM TRACES WHERE LocalTime >= "2006-05-23" and LocalTime <= "2006-05-24" and Interval = "1"
2 record(s)
5/23/2006   0
5/24/2006   .5313... (12:45:13)

Problem is, that time returned for the 24th is actually for the 23rd! It was on all day the 24th (which shows on the 25th!) so i could test it.

We could make this work with an EXTERNAL VB application, but it is required that we keep it "Lookout".  ActiveX is not wanted, but unless there is a way to make this work, maybe the only way to go...

Thanks
Mike

[Khalid]

This is just a guess, but have you tried specifying the end time as the same day but also specifying the time as 23:59:59?

WHERE LocalTime >= "2006-05-23" and LocalTime <= "2006-05-23 23:59:59"

-Khalid

[Mike@DTSI]

Yes, same response, 0 for the 23.

Thanks

[Bob_L]

WHERE LocalTime >= "2006-05-23" and LocalTime <= "2006-05-23 23:59:59"

Is it possible that  LocalTime "2006-05-23" has an implied time part of 00:00:00? If so, isn't the period actually less than a full day. (1 sec less?)

It would be interesting to test with an interval other than 1. (ie 00:01:00)

Also the results from a full 24 hour period might work differently.

ie WHERE LocalTime >= "2006-05-23 00:00:00" and LocalTime <= "2006-05-24 00:00:00"

Stated another way, there are 24 hours in one day. But you need to specify 25 hourly times to  specify those 24 intervals.

Regards,

Bob

[Mike@DTSI]

Intervals less than 1 day (1 hour or so) give the times for that hour on the 23

from 23 to 24.  But for full 1 day, the previous days end time is on the next day, like ive shown.

And of course, you cant show the current runtime as the day isnt over... 

Thanks
mike

[Ryan.S]

Is the system time in Lookout correct? Object explorer->System->Time.

[Mike@DTSI]

Yes it is correct, as it was on that day also.

Just for more info, i have tried my own vb application, the NI vb example and using the lookout sqlexec/datatable.  All same results.

I was hoping that i wouldnt have to "fake" the times, as this can get very intensive.

Message Edited by Mike@DTSI on 05-30-2006 01:38 PM

[Ryan.S]

I'm a little confused.
The result in example 2 is 5/24/2006   .5313... (12:45:13)
Do you mean it should be 5/23 12:45:13? 
If you trace the data in MAX, what's the date for that? 5/23?

[Mike@DTSI]

You got it, if i view the trends data is correct for the 23rd, but using SQL i have to retrieve 2 days to get the data for the 23rd.  To add more to the equation, if i set start day as 5/24 end 5/26, the data for the 24th shows INCORRECTLY, the 24th runtime shows as the 25th.

so rundown
5/23 runtime is on 5/24
5/24 is on 5/25, but 5/24 shows the WRONG DATA which is supposedly the 23rd.

Getting fun aint it 🙂