Weird Date number

[mdillane]

I currently put data in to a database using the following code, as per the manual. It works fine, putting the date and time in and the value, but when i use this code below as in the manual i dont get back a date i get back the following number, 1222098436, this increments by one for each second.

I have tried casting the number into a date, string, formatting etc etc, but it doesnt work, can anyone tell me how to cast this number.

The date in the database is fine, but as i said it only changes when i retrieve it and put it into a virtual channel as required.

//******************************

global test

v.batch.ClearHistory()

v.thetime.ClearHistory()

dbase = db.Open("DAQData")

qr = db.Query(dbase,"select * from DAQ1")

global counter

counter = 0

while (!db.IsEOF(qr))

test = db.Field(qr,"Time_Water_Storage_Water")

v.thetime[counter] = test

counter++

db.MoveNext(qr)

endwhile

db.CloseQuery(qr)

db.Close(dbase)

//*******************************

[AzeoTech]

That number is standard DAQFactory time, which, I might as is standard Windows time, even though some of their products like Excel and Access use a different format, for good reason. It is seconds since 1970. I'm not sure why you want to "cast" it. You want it in that format in DAQFactory because that's what DAQFactory uses. If you want to see it in human readable format, use the formatdatetime() function. At the command line, for example, doing:

? formatdatetime("%c",1222098436)

returns (at least for me in the U.S.):

9/22/08 15:47:16

I also recommend reading the preceeding post in this logging forum on how to read a database: http://www.azeotech.com/board/index.php?showtopic=3384 Here I talk about a new function called QuerytoClass() which works a bit better than the old method you are using.

[mdillane]

I have tried the formatDateTime and it wont work...i need it in readble format for SQL queries (Thats another issue altogether). We have to query the logged data in the database for a given time period....so for example, we log for three months and want the info for week 3 to 6 then 2 to 9 etc etc.

I am using an access database.

I have tried formatting it just after the record is retrieved during the sequence, then assigning to a variable, etc etc... it just wont work.

Am i correct in assumimg that DAQ will Query a database for dates using its own format or is there another format for this. Do you use Hash marks, commas???

Dates and SQL can be tenuous at best any help would be appreciated.

e.g.

SELECT Time FROM tableName WHERE Time >=#24/02/2008# AND Time <=#26/02/2008#

I am sure you get the idea

thanks in advance

[mdillane]

okay...

I am getting somewhere...but i have a question

I am extracting data from the table to the virtual channel.

I then retrieve the number and do the following

global string varTimeFrom

varTimeFrom = v.timeFrom[0]

varTimeFrom= formatDateTime("%c",v.timeFrom[0])

or

global string varTimeFrom

varTimeFrom = v.timeFrom[0]

varTimeFrom= formatDateTime("%c",varTimeFrom )

neither work...

when i do this from the command line it works:

formatDateTime("%c",v.timeFrom[0])

I really need to convert these number into dates during a sequence...

I am doing the following...

[AzeoTech]

First, formatdatetime() requires a number, not a string, for the second parameter, which is why you can't use varTimeFrom in it as you declared this as a string.

Second, formatdatetime() has a whole bunch of other specifiers you can use to format the date and time however you want. %c just uses the Windows settings, but if you look in the help file you'll see there are ones for just about anything you'd need. So, you can format without any punctuation.

That all said, I'm a little unclear what you are doing. Are you reading the time from the database, or are you querying the database for a time range? If you are querying on a time range, and the time data is stored in DAQFactory time (i.e. 1222098436) then you want to put numbers in the query too, as the field in the database is numeric, not date and time. You can use the strtodate() function to convert human readable time to DAQFactory time.

[mdillane]

What i am basically doing, is reading an electricity meter and displaying its information from the MODBUS, volts, amps, kw,kwh etc...

These details are then being displayed on one of the pages.

I am also logging, the details to an access database. So for example, the kw are being recorded every second and logged. Once this in the access database i can open access, and see the value of the kw and the date and time it was logged.

I then, using DAQ, need to get the information back to the display, things like total kw for one month, one day year so far, month so far, etc etc. When i get the data back, i use a select * statement at the moment to get it all back and fill a virtual channel with the information from the dates and the one channel for the data...

I am now at the experiemtnal stage of trying to:

A:) Query the database for specific dates and values.

B:) Tring to convert the DAQ time to a meaningful time (Human).

As for point A, i am wondering whehter i use the DAQ time to query the database or Human Time, in the SQL statement...

and also when i do the following sequence i only get back one number, which is 9 at the moment:

global varTimeFrom

varTimeFrom = formatDateTime("%c",v.timeFrom[0])

It doesnt matter what i use "%j" or "%y" or any of the others i only get back one number...I need to be able to do this conversion to perform the many date calculations i require to do...

[AzeoTech]

OK, first you might consider using Channel Persist for this. Its much easier. But, if you want to pull from logs instead, then you are on the right track.

a) assuming you used a logging set to log and you do not have Use Excel time checked, you should use DAQ time in your query. Treat it as a number.

in your script:

global varTimeFrom

varTimeFrom = formatDateTime("%c",v.timeFrom[0])

you have varTimeFrom declared as a number, but formatdatetime() returns a string. It has to, because 9/25/08 is not a valid number. But, DAQFactory does some automatic casting when it can, so it is taking 9/25/08 5:23:05 and trying to convert it to a number. It converts the 9, but then gets to the slash and throws up its hands and stops because it can't convert the slash. This is why you always get 9. If you did:

global string varTimeFrom

varTimeFrom = formatDateTime("%c",v.timeFrom[0])

It would work correctly.

[mdillane]

Of Course...

I have just slapped my head and gone Doh!

I am forget that you can assign a string and a date is not a number...

Thanks again for all of your help on this...

i am a baffoon..