Example Code

CCI Time to XL.vi

Code and Documents

Attachment

Converting LabVIEW time to Excel format is a subject that frequently repeats on the forum and the current example code linked from

http://digital.ni.com/public.nsf/allkb/04BA60F0C2DE768A86256CED007E528B

is noted to ignore daylight savings time.  I've had a version of the routine in my pocket for several years now and thought I might share it more openly


"Should be" isn't "Is" -Jay

Example code from the Example Code Exchange in the NI Community is licensed with the MIT license.

Comments
JÞB
Knight of NI
Knight of NI
on

PNG of the above code

Time to Excel.png


"Should be" isn't "Is" -Jay
rrutan
NI Employee (retired)
on

Just an FYI...you can create a document and add the VI as an attachment, this will allow you to embed the picture in the same document, plus if you use a VI Snippet...it will auto detect the version of LabVIEW.  Just thought I'd let you know.  Thanks for sharing 😃

zhao525fly
Member
Member
on

Thanks!! But could you share the V8.5 VI, please? I am suing V8.5.1, so I still cannot use this awesome VI.

CoastalMaineBird
Trusted Enthusiast
Trusted Enthusiast
on

I don't understand the point of converting from TimeStamp to DateTime Record and back.

The TO UTC input of TimeStamp to DTrec is default FALSE, so wiring it is unnecessary.

The IS UTC input of DTrec to TimeStamp is also default false.

If I compare the output of DTrec to TimeStamp with the original input, they are equal in all cases that I run.

The comment says "Toss out Zone", but there is no zone encoded in the TimeStamp AFAIK.  A Timestamp is an I64 and a U64.  It contains numerical info only (sections and fractions of seconds), correct?

Is there a timezone encoded in the LSBs that gets lost in the conversion?

If not, then I don't see the point.

Steve Bird
Culverson Software - Elegant software that is a pleasure to use.
Culverson.com


Blog for (mostly LabVIEW) programmers: Tips And Tricks

CoastalMaineBird
Trusted Enthusiast
Trusted Enthusiast
on

If I cast the TimeStamp to a cluster { I64 | U64 } and do arithmetic on the cluster, then cast it back, I see no changes in the output other than the arithmetic that I did.

That suggests that there is no hidden timezone encoding.

Therefore the conversion to DTrec and back is not accomplishing anything.

I know from experience that a TimeStamp value recorded in CST, and displayed in EST shows an hour's difference from the same value displayed in CST.  That suggests to me that the process of displaying a TimeStamp value requires knowledge of the current timezone, from outside the value itself.

I know that TimeStamps were not always { I64 | U64 } .  I wonder if perhaps the timezone was once encoded into the TimeStamp structure (whatever it once was). In that case, the above conversion would indeed "Toss out Zone".

All this to save a microsecond or two.

Steve Bird
Culverson Software - Elegant software that is a pleasure to use.
Culverson.com


Blog for (mostly LabVIEW) programmers: Tips And Tricks

CoastalMaineBird
Trusted Enthusiast
Trusted Enthusiast
on

For the record here is my revised code.

X.png

With the CONVERT operations in there, it timed at 2.59 uSec.  Without them, it's 0.88 uSec.

The SECperHOUR is a constant 3600, the SECperDAY is a constant 86400.

Steve Bird
Culverson Software - Elegant software that is a pleasure to use.
Culverson.com


Blog for (mostly LabVIEW) programmers: Tips And Tricks

ncherkasov
Member
Member
on

For some reason, base time is 6am, but not 12 am as in the picture. As a result, the time should be shifted by 6 hours forward

JÞB
Knight of NI
Knight of NI
on

ncherkasov wrote:


                       

For some reason, base time is 6am, but not 12 am as in the picture. As a result, the time should be shifted by 6 hours forward


                   

That is a known issue.  Time constants do not travel well!  The vi is really only useful where the deployment is in the same timezone as it was developed in.  The version attached was built in Central Time.  You would need to adjust the timestamp constant for your location and remember to use it in that zone only.  That being said, @95% of the time the vi IS useful but, it does have limitations.  There is a longer explaination here where I asked for my own idea to be rejected.


"Should be" isn't "Is" -Jay
Contributors