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 11
(1,323 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 11
(1,302 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
My Blog
Message 3 of 11
(1,281 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 11
(1,266 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 11
(1,262 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 11
(1,250 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 11
(1,237 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
My Blog
0 Kudos
Message 8 of 11
(1,218 Views)

Hi. Where you able to find a solution?

 

Thank you.

0 Kudos
Message 9 of 11
(577 Views)

I'm not using the NI Database toolkit but that doesn't matter. I don't think, that there is a way around this issue. Seems to be located in some software layer before labview. The date is there even if using the GetString method to fetch the database records.

 

throw the date away in labview.

 

 

0 Kudos
Message 10 of 11
(559 Views)