Problems of Saving Date/Time to Database and Displaying It in Graph.

[jerrydio]

Software used: LV8.0, MS SQL Server, LabSQL

I want to save my data acquired from DAQ card to database(SQL Server) by using LabSQL. My scan rate is 200/s. The data saving is all OK, but I have several problems when I want to save the exact time when the data is acquired.
Problems:
1. I don't think I can save the date and time( timestamp) as yyyy-mm-dd hh:mm:ss, so I turn the timestamp data into DBL and save to the database. I am quite wondering if this way good? Is there other better way I can use to save the timestamp data easily? And if there is, how to use the timestamp data to query the database?

2. When I retrieving the data form database, I want to use the data to make a chart or graph in LabVIEW. The X axis is the date and time when I acquire the data. But I early save the date and time as DBL, and now I want to make the X axis with way of yyyy-mm-dd hh:mm:ss. So I turn the DBL into timestamp, but I just can not use the timestamp to built an XY graph. Then I turn the timestamp into time string, but is also can not be used to built XY Graph. Is there any way can the time data be displayed as X axis scale in a graph?

Thanks for any advice or help.

Huang

[DavidJCrawford]

Huang

You can convert a Timestamp to a DBL then display that on your graph using the Format and Precision Advanced Editing functionality for the X-Axis.

Have a look at this example in 8.0 and let me know if this helps you or not.

David

[Dennis_Knutson]

And, you can certainly save the timestamp in the database as yyyy-mm-dd hh:mm:ss if the column is defined as a datetime data type. If I remember, all you have to do is convert the LabVIEW timestamp to a string and write it as such to SQL Server. The advantage of storing it in the database as a real date/time value is that it makes it much simpler to do queries based on real dates or time (i.e. list all results between January 1, 2005 and March 2, 2006).

[DavidJCrawford]

Absolutely, I do exactly that with an Access database sorry Dennis I know you are not a fan

Message Edited by David Crawford on 06-01-2006 04:10 PM

[Aitortxo]

Hi, Huang:

----
I don't think I can save the date and time( timestamp) as yyyy-mm-dd hh:mm:ss
---
You can save it in SQL Server, and seconds can have up to 3 digits of precision

you can do it converting a string to datetime :

INSERT INTO tablename (..., DateTime_column, ...)
VALUES (..., CONVERT(DATETIME, '2006/06/01 18:03:25.234'), ...)

Hope it helps,

[jerrydio]

Thank you all a lot!

David, your example helps a lot, and I find timestmp also can be used to build XY graph, and the time data can be displayed on X axis with the "relative time" type.

[jerrydio]

Dennis and Aitortxo, thank you a lot, it works fine with your ways. ^_^
 

[Dynamik]

Hi Huang,

      In addition to the approaches above, you might store your timestamp-DBL as a DBL, but in the DB's time format.  MSAccess and SQL Server use a DBL to store times as the DAYs since 12am DEC 31 1899.  I'd be interested to know if MYSQL doesn't use the same format.  On the down-side you'll have to convert LabVIEW's timestamp-DBL to the DB's timestamp-DBL, and when you want to see the string-representation of the time-DBL stored in MSAccess, you'll have to use the function CVDate(YourDBL) (SQL Server use CAST(datetime, YourDBL)).

... but on the UP side, no DBL to string conversion (with accompanying loss of precision) is necessary when storing the timestamp, and no string to DBL conversion is necessary when recovering the LabVIEW representation (in this case there's a DBL to DBL conversion).  This method also overcomes a problem that can occur if storing times as strings in a country where daylight-savings-time is used.  Here, twice a year, timestamps stored as strings will jump forward (or backward) one hour, unless special precautions are applied in the OS.

Cheers.

Message Edited by Dynamik on 06-03-2006 02:14 AM

Message Edited by Dynamik on 06-03-2006 02:15 AM