LabVIEW
Database Variant to Data, issue with Time conversion
I am getting data from a database, where two columns have time data in string type, in the form hh:mm:ss. For reading the database I use the MySQL ODBC v8.0 connector, and the database is managed by XAMPP, on localhost.
The connection works fine, but the time is converted to a Date format: for example '06:00:00' is converted to '24/4/2022 06:00:00', i.e. it adds today.
Did this happen to anyone, how did you solve it?
Using LabView 2015 (32-bit).
There are many layers of software your data has to go through before it arrives at LabVIEW, so it is important to analyze where this “change” happens.
What’s the datatype of the column in your database itself, It could be a Date, Time or DateTime but configured to only show the time part. That still doesn’t make it a time only column!
Then the data goes through the MySQL ODBC driver, which I assume would be ok.From there through the Microsoft ODBC to ADO/DAO bridge. Afrter that to the LabVIE Active X interface. Have you probed the variant data coming from those VI’s? I’m sure they show already the date too and the DB Variant VIs are not doing anything like the title indicates. The ‘error’ must happen before that!
@Jamosgee ha escrito:
I don't really know for sure, but I'm guessing in the database the time is not stored as "string", but "time". You should understand that the data type is not defined by how write it to the database, but in the database schema. You can the MySQL admin tools to verify this. It should be possible with the browser based interface you are using.
Please not that LabVIEW does not have a time only datatype like MySQL. So when you convert the time you get in LabVIEW you will end up having some date on it.
That’s factually wrong. LabVIEW distinguishes between absolute time and uses a timestamp datatype for it and a relative time which it uses a double floating point for.
However that is completely irrelevant here, since the OP tries to look at the strings and there are no datatypes in string form anymore. What would be interesting though is to see what the variant display shows. In the variant, the original data as received from the Active X interface is visible and if you right click on the variant display and select “Show Datatype” it will even display what datatype the individual values are in. This will almost certainly show you that the date & time information is in there and the Variant to Data node simply converts what it gets and doesn’t invent anything.
My bet is still on that the actual database column is defined to be a date-time value but the entry display is somehow configured to only show time But we need to see the actual database definition/schema to be sure.
