LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

"Variant To Data" VI converts date/time inaccurately.

Solved!
Go to solution

 

The extracted data is a timestamp column in a microsoft access 2007 database.

As shown below, the variant says "1:46:45"

 

variantToData01.png

 

But the timestamp converted from the variant is 1 milisecond ahead of the variant.

It's now "1:46:44.999".

It is shown below.

 

variantToData02.png

 

How can I make the conversion accurate?

0 Kudos
Message 1 of 18
(5,259 Views)

Hi iCat,

 

I think what you are seeing here is an 'inaccuracy' in the variant probe display.  It will not display the millesecond values for the timestamp and as such appears to be rounding.  I have tried to replicate this using my own machine (LV 2011 SP1) however I do not see the rounding behaviour.

 

- In your case, isn't the .999 more accurate than the rounded value?

- Which version of LabVIEW are you using?

- Could you attach a snippet of the variant timestamp?


Regards,

Peter D

0 Kudos
Message 2 of 18
(5,239 Views)

Pete.Dunc //

 

In the database, it is stored as "2010-04-16 01:46:45.000" but not "2010-04-16 01:46:44.999".

 

"Variant To Data" subtracted a milisecond from the variant and returned the subtracted timestamp.

 

And I use LabVIEW 2009 SP1.

 

What do you mean by a snippet of the variant timestamp?

0 Kudos
Message 3 of 18
(5,230 Views)

To create a VI Snippet of the data I would do something similar to the following:

 

CreateVISnippet.png

If you post the snippet, then we can see the data you are using and reproduce the issue.  It is also possible to 'round your timestamp' to the nearest value - although you will have to extract some of the information as a string before the conversion.


Regards,

Peter D

Message 4 of 18
(5,218 Views)

Hi,

 

    Test replace "Variant To Data" by "Database Variant To Data".

 

Best Regards,

Luis A. Mata C.
Ing. Electrónico
Whatsapp: +58-414-1985579
BBM Pin: 2B83E99A
Thanks: Kudos
0 Kudos
Message 5 of 18
(5,207 Views)

"DB Variant To Data" displays the same behavior as "Variant To Data" as shown below.

 

VarianToDataBug.png

 

I'm sorry for using a korean version of LabVIEW.

The company gave me no choice but to use the korean version.

 

I talked on phone with an NI instrument support.

 

He told me that because access database doesn't store miliseconds in date/time data type, LabVIEW has to approximate the exact timestamp hence substracts 1 milisecond from the original variant.

 

How do I insert a milisecond value into the date/time variant obtained from microsoft access database and convert it to the exact timestamp?

0 Kudos
Message 6 of 18
(5,167 Views)

To increment by a millisecond - convert the timestamp to a double precision numeric, add the value and convert back to a timestamp. Smiley Happy

 

 

IncrementTimeStamp.png


Regards,

Peter D

0 Kudos
Message 7 of 18
(5,149 Views)

I actually found a bit of a weird bug with this one.  You should typically be able to just add a numeric value to the timestamp (without the conversion shown above).  However this does not work when add a millisecond to a value which ends with .999

 

Example:

 

IncrementTimeStamp.png

 

BugFP.png


Regards,

Peter D

0 Kudos
Message 8 of 18
(5,145 Views)

The plot thickens... this has been filed as corrective action request #351067.

 

BugTesting.png


Regards,

Peter D

0 Kudos
Message 9 of 18
(5,141 Views)
Solution
Accepted by iCat

This is how I solved the millisecond problem with microsoft access.

 

Because datetime type of microsoft access database has a precision of up to a second,

some datetime records are accurately converted to LabVIEW timestamp, and other datetime records are converted with 1 millisecond deviation(it could be plus or minus) to LabVIEW timestamp.

 

I didn't want to discriminate between those converted accurately and those done inaccurately since it could lead to errors.

So I did as below.

 

reply01.png

 

These are the first 6 columns of recordset.

recorded_time is an "access" datetime column.

year(datetime) returns year as an integer of 4 numbers, month(datetime) returns the month as an integer between 01~12, and so on.

 

And I created a SubVI that processes the first 6 columns of a recordset and returns a timestamp.

 

reply02.png

 

As you can see the fractional second is set to 0 which means .000.

Now every datetime record has .000 at the end, and no datetime record is converted to LabVIEW timestamp inaccurately.

Message 10 of 18
(5,125 Views)