SQL Data not converting correctly using Variant to Data VI

[computrwkr]

Hello

I created a VI that will grab data from a MySQL database and then plot a graph of the data. I got everything to work great except for the DATE doesn't convert correctly going through the "Variant to Data.vi".

I have attached a printout of what I see when I run the vi; the data (date) comes out of the database correctly but when it gets converted, the seconds are only 5 digits long (results) - which when the seconds get transfered to a timestamp - the date comes out as 1904, etc.

Any assistance or suggestions would be appreciated.

Ian H.

[RavensFan]

I can't run your program because I don't have DB tools installed, nor your database.  But I am looking at your data.

I think a few things are going on here.

1.  The value in the first column of your recordset seems to be a value in days.  This gets cast to a double precision number.  In the loop, the "To Time Stamp Function" takes that value and interprets it as seconds.  Take a look at the help for that particular function.  It says takes in number of seconds since 1/1/1904 in Universal Time.

2.  I believe the time value is getting stored in the database as number of days since 1/1/1900, and probably also based on local time.

So I don't think there is an issue with the way the DB tools are working, or the Variant to Data function.  Just a difference in the way the dates/times are stored in the database as opposed to the way the Labview date/time functions are working with the values.

See attached where I converted the value in days to the value in seconds.  Either the conversion needs to be done (with possibly a bit more to convert to a desired time zone and get rid of that 4 year difference in base time), or the Variant to Data function may need some sort of cluster wired in to get a Time value in days, rather than using a double precision array.

Message Edited by Ravens Fan on 09-30-2007 05:42 PM

[computrwkr]

After thinking about this for awhile, I came to the same conclusion.

I found out if I multiplied my seconds by 83190.8437, the number that got outputed was extremely close to the date I wanted. Of course, the amount of time I am off compounded over 4 days - amounts to 3 hours. Which is what it appears my graphs is off by on a total of 4 days. But no worries.

Thanks for your assistance,

Ian H

[RavensFan]

I don't know why you came up with the value of 83190.8437.  Their are 86,400 seconds in a day (24 x 3600 in the picture I posted above).  And the time base is 1/1/1900 for the database while it is 1/1/1904 for Excel.  So you just need to subtract 1096 days from the first number.  (By the way, most computers think 1900 was a leap year even though it technically wasn't.  They will often miscalculate days of the week prior to 3/1/1900).

I attached a VI (LV 8.2) that does the job.  I also put in another control that allows you to adjust for time zone differences in case you want the value to appear in a time zone other than UTC.