topic Re: LocaTime not displaying in SQL in Lookout

LocaTime not displaying in SQL

Mike@DTSI — Fri, 20 Jul 2007 18:29:50 GMT

Creating reports in Lookout 6.0.2 (Citadel 5). Cannot retrieve the values of LocalTime.

Example SQL:

"SELECT MATH_ETM(""mikedesktop/TestDB/OnOff""), MATH_STARTS(""mikedesktop/TestDB/OnOff""), MATH_STOPS(""mikedesktop/TestDB/OnOff""), LocalTime FROM IntData WHERE IntInterval='01:00' AND LocalTime BETWEEN '2007-7-20 11:23:00' AND '2007-7-20 11:30:00'"

Returns (Example):
39.3372, 3, 2, 0 (On for 39.3~ seconds, started 3 times, stopped 2, no localtime)
57.11,1,1,0 (On for 57.11~ seconds, started 1 time, stopped 1 time, no localtime)

Any ideas?

Thanks
Mike

Re: LocaTime not displaying in SQL

Ryan.S — Mon, 23 Jul 2007 02:52:36 GMT

Do you use Datatable object in Lookout? Take care that LocalTime is txt format. So if it's column D, it should be D1.txt, D2.txt...

Re: LocaTime not displaying in SQL

Mike@DTSI — Mon, 23 Jul 2007 16:15:47 GMT

Ok, cleared that problem (sure wish NI knew how to document this in the changes :\)

Next problem,

If i have 2 logged variables (Var1, Var2)
and var1 runs 12:34 (HH:SS), Var2 12:20 (HH:SS)

I receive 12:34 and 03:34

No matter what points, my numbers are wrong. HyperTrend shows the runtimes correctly, but using the SQL pull (Which works great in LO 5.1 BTW) they are incorrect.

Seudo-code SQL:
SELECT MATH_ETM(Var1), MATH_ETM(Var2), LocalTime FROM MyDatabase WHERE LocalTime BETWEEN '2007-07-01 00:00' AND '2007-07-31 24:00' AND IntInterval=1

Re: LocaTime not displaying in SQL

Ryan.S — Tue, 24 Jul 2007 09:34:38 GMT

See the attachment. The right part is the raw data I retrieved in Excel. The left part is the Lookout datatable values. In Datatable, I select MATH_ETM(var) from IntData Where LocalTime Between 17:04:00 and 17:09:00, AND IntInterval = 00:01:00.

One thing needs to be clarified is that when I query MATH_ETM(var) from 17:04:00, the first value returned is calculated from 17:03:00 to 17:04:00.

You can see that the values I got in Lookout Datatable are correct. You can also do it in Excel and then calculate by yourself.

Re: LocaTime not displaying in SQL

Mike@DTSI — Tue, 24 Jul 2007 17:13:21 GMT

I am aware that it selects the data before the LocalTime syntax, we simply skip row 1.

Here is a screenshot from the same computer i have the problems on. I used the exact same start date (06/2007) with the same data members.
NOTE: The 'Date' column on the Lookout 6 is wrong, we have since solved that issue. Also, the estimated times on the tops of each image are incorrect, not related to this issue.

Thanks
Mike

Re: LocaTime not displaying in SQL

Ryan.S — Wed, 25 Jul 2007 02:56:41 GMT

So, do you mean the data in Lookout 6.0.2 are incorrect, while those in Lookout 5 are correct?

Since Lookout 6.0.2 uses Citadel5, while Lookout 5 uses Citadel4, are you querying a Citadel4 database in Lookout 6?

From the screenshot of Lookout, I can't find anything wrong. That's why I suggest you to retrieve the raw data from the same database in Excel. In this way, you can find out whether the results in Lookout are correct or not.

Re: LocaTime not displaying in SQL

Mike@DTSI — Wed, 25 Jul 2007 16:05:17 GMT

The Lookout 6 database is the same database, just converted to Citadel 5.
Is there a change log to tell the differences between Citadel 4 and 5? Seems like there are quite a few differences.

The numbers in minutes, seconds, hours, etc. are ALL different between the two.

Thanks
Mike

Re: LocaTime not displaying in SQL

Ryan.S — Thu, 26 Jul 2007 05:38:45 GMT

Mike, I want to reproduce your issue on my computer. Could you upload your citadel 4 database to ftp://ftp.ni.com/incoming, and send an email to lookout.support@ni.com with the sql statement you use. If the database is too large, you can archive part of it to an new database. Then let me convert it into Citadel 5 and query both 4 and 5 to see the results.

Re: LocaTime not displaying in SQL

Mike@DTSI — Fri, 21 Sep 2007 18:23:46 GMT

Sorry i havent responded.

Here is where we stand.

The ETM statements -MATH_ETM(TO_DISCRETE("""mikedesktop/Server/PumpTMP/BC""))- returns the value in SECONDS (~40741 seconds =~11.31 hours (11:19))

The date returned is wrong, the runtime for 09/21/2007 (today) comes back with the date 09/22/2007 from the sql requests.

So while the data is NOT wrong, it is not placed correctly.

Mike

Re: LocaTime not displaying in SQL

Mike@DTSI — Fri, 21 Sep 2007 22:17:03 GMT

Wow that will piss you off... making changes and wham, over 50 I/O, ok no biggy i can use my unlimited 5.1 LO, err wrong, i cant save my process file. Good job developers. Atleast make it so we can save our work. BS,

Found part of the issue with the dates not lining up, they are 2 rows off (data for 09/20/2007 shows on 09/22/2007)

Message Edited by Mike@DTSI on 09-21-2007 05:17 PM