UTC time to OLE date...

[Vasia]

Hi,
I need to be able to transfer UTC time  format into OLE date  format. Is it possible?
 Thanks.

[smercurio_fc]

The OLE date format is a double-precision floating point number that counts the time from 30 December 1899 00:00:00. Thus, a value of 0.0 corresponds to 30 December 1899 00:00:00. The UTC date that is returned from the LabVIEW functions is the number of seconds elapsed since 12:00 a.m., Friday, January 1, 1904. Thus, in this case, a value of 0.0 corresponds to 01 January 1904 00:00:00. This means you have an offset between a LabVIEW timestamp and the OLE date. Basically, whatever date you get from LabVIEW you need to add the number of seconds between 30 December 1899 00:00:00 and 01 January 1904 00:00:00 to get the corresponding OLE date value (in floating point).

[RSibagatullin]

Is it something like I have attached? So the  in my Vi shows the OLE date formated  current time right?

[smercurio_fc]

You will also need to divide by the number of seconds in a day since it turns out that the OLE date value is in terms of days, rather than seconds:



NOTE: As with all things you should verify this before accepting it as "canon". I did some quick checks with Excel, and the values seemed to correspond but I did not perform an exhaustive analysis.



Message Edited by smercurio_fc on 06-23-2008 02:12 PM

[RSibagatullin]

Interesting. I was curious how do you check the resulting value in Excel? Are you using the DATEVALUE or someting?
It appears that OLE date  consists of 8 bytes like this  AT0, AT1, AT2, AT3, AT4, AT5, AT6, AT7 . Where ATx is 8 bits. How can I format my labview generated OLE date into this format?
The reason why I am asking  is that I need to prepare a packet byte by byte:

AT0, AT1, AT2, AT3, AT4, AT5, AT6, AT7,                       Acqusition time

0,0,0,0,                                                                         Item_Type_ID (0->packet wrapper)

28,0,                                                                             Packet length, including wrapper

0,0,0,0,                                                                         Tool string number

242,255,255,255,                                                           Item_Type_ID (-14->Start acquisition command)

16,0,0,0,                                                                       Net packet length

4,0,0,0,d,c,b,a                                                               Encoded IP Address if your computer has IP address a.b.c.d.

DEFA,0,0,0,                                                                  Decimation factor. Transmitter frequency is 1000/DEFA. DEFA is a number between 1 and 255 in this case.

0,0,0,0                                                                          DC_AUX_Flags. MUST be all zeros.

This packet needs to be send via UDP to another hardware.

 

[smercurio_fc]

---

@RSibagatullin wrote:
Interesting. I was curious how do you check the resulting value in Excel? Are you using the DATEVALUE or someting?

---

You can change the format of the cell to just General and it will display the value. The main problem is with the interpretation of numbers into dates. For instance, Excel kept being off by 1 hour because of Daylight Savings here in the Central time zone.

Also, I get 126,316,800 rather than 126,291,600 for the offset. I'll need to check on this, but I don't have time right now.

---

It appears that OLE date  consists of 8 bytes like this  AT0, AT1, AT2, AT3, AT4, AT5, AT6, AT7 . Where ATx is 8 bits. How can I format my labview generated OLE date into this format?

---

The date is just a floating point value of 8 bytes. As long as you use a DBL you'll get that. You can pipe a timestamp into the To Double Precision Float function to get a DBL, as shown in my example.

[smercurio_fc]

OK, ignore my previous comment about 126,291,600 - I was looking at something else. Trying to deal with the timestamp datatype is a pain because of the business with Daylight Savings time and time zone offsets. Here's a UTC datetime record converted to OLE date format:



And here's what Excel shows for that value formatted as a date:



So, they seem to match.


Message Edited by smercurio_fc on 06-23-2008 05:13 PM

[RSibagatullin]

smercurio thanks for your exlanation.  I have one more question. So I am trying to buid the packet for UDP  that I have described above. I noticed that I can't set up the U8 integer to 255. How come I can't? I thought that U8 uses  all 8 bytes for the unsigned value so it is supposed to be able to get set to 255 (bin 11111111) and that  is what the LV help says. But in the VI attached LV does not allow to set up the value for U8 to 255.




Message Edited by RSibagatullin on 06-24-2008 11:44 AM

[smercurio_fc]

Because your constants are I8, not U8. Sticking a conversion to U8 will not magically change a 127 to 255. You need to make the constants U8 and delete the conversions to U8.

Also, you should not be creating your packet stream this way. If you'll notice your Build Array input terminals have coercion dots (except for the first one). This means that each value is being upcast to a DBL. This is not what you want. You need to convert the DBL to an array of U8 and concatenate it to the other U8 elements. Then, convert the array of U8 to a string to send via UDP:

Message Edited by smercurio_fc on 06-24-2008 12:53 PM

[RSibagatullin]

Thanks a lot for your sample code. I did not even know that the type-cast thing existed in LV.
So  I think I finished the packet builder but I still get the the coercion dots. Does that mean I am fatally corruptiong my bytes or it is just casting related? Thanks.



Message Edited by RSibagatullin on 06-24-2008 07:25 PM