LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

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).

rntaboada_3-1650849374468.png

 

 

rntaboada_0-1650848877483.png

 

rntaboada_2-1650848975006.png

 

 

rntaboada_1-1650848938823.png

 

 

0 Kudos
Message 1 of 8
(272 Views)

Do you have access to the raw data from your database ? 

It seems you are using a software and it can happen that it is formatting your data.

0 Kudos
Message 2 of 8
(251 Views)

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!

Rolf Kalbermatter
Averna BV
Message 3 of 8
(230 Views)

Hello Rolf.

Well, regarding your first question, the time data is just that, I wrote that data so I can assure you that it only contains the time, without anything additional.

0 Kudos
Message 4 of 8
(215 Views)

Hello Defaphe,

Between LabView and XAMPP is the MySQL ODBC driver. The raw data is the one shown in the phpMyAdmin screenshot, data that I wrote myself.

0 Kudos
Message 5 of 8
(211 Views)

Is your output the same by using Microsoft Access (or directly in MySQL database) instead of phpMyAdmin ?

Your phpMyAdmin Screenshot is, in my opinion, a formatted display : you can write a time only data but it can be stored as date and time using the current day as reference and your input time.

0 Kudos
Message 6 of 8
(199 Views)

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.

0 Kudos
Message 7 of 8
(186 Views)

@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.

Rolf Kalbermatter
Averna BV
0 Kudos
Message 8 of 8
(167 Views)