1

I am trying to parse a file created by another software, but I cant identify a pattern on how this datetime is saved. There doesnt seem to be any consistency.

Programming language of the software is C++, so I already tried the unix/epoch method without success. Maybe someone does recognize a pattern or can give me a hint on how to approach such a problem.

1/1/2000 12:00:00 AM    00 00 00 00 C0 D5 E1 40
1/1/2000 12:00:01 AM    C9 45 18 00 C0 D5 E1 40
1/1/2000 12:00:10 AM    D6 B9 F2 00 C0 D5 E1 40
1/1/2000 12:01:00 AM    06 5B B0 05 C0 D5 E1 40
1/1/2000 07:00:00 AM    55 55 55 55 C9 D5 E1 40
1/1/2000 07:01:00 AM    5B B0 05 5B C9 D5 E1 40

1/2/2000 07:01:00 AM    5B B0 05 5B E9 D5 E1 40
1/3/2000 07:01:00 AM    5B B0 05 5B 09 D6 E1 40
2/1/2000 07:01:00 AM    5B B0 05 5B A9 D9 E1 40 
3/1/2000 07:01:00 AM    5B B0 05 5B 49 DD E1 40

3/1/2001 07:01:00 AM    5B B0 05 5B E9 0A E2 40
3/1/2002 07:01:00 AM    5B B0 05 5B 89 38 E2 40
8
  • 3
    To the close-voters: I think this is one of the rare questions that could be a "how do I write this code" question, but there does exist I real conceptual answer. Oct 30, 2022 at 16:20
  • When you say you tried the epoch method, does that include an alternate time 0, and alternate resolution? Oct 30, 2022 at 17:03
  • The numbers are double precision, counting days since Jan 1st 1900.
    – gnasher729
    Oct 30, 2022 at 21:22
  • The day count on Jan 1st 2,000 is 36523, so apparently the base is midnight Jan 1st or start of day Jan 2nd 1900.
    – gnasher729
    Oct 30, 2022 at 21:28
  • There aren't a wide enough range of dates and times. A whole day appears to be a value of 2 00 00 00 00, suggesting that a 12 hour period is fundamental. The difference between 3/1/2000 and 3/1/2001 is 2D A0 00 00 00 00, where 2DA is 730, which is 2*365. I suspect also the highest components are not part of a day count. It also seems clear that the 4 lower bytes are exclusively a time component.
    – Steve
    Oct 30, 2022 at 22:26

3 Answers 3

5

This is a "COM date" or "OLE Automation date", which is a reasonably common format under Windows. It's defined as a double-precision floating point number whose value is the number of days (including fractional days) since midnight on December 30, 1899. I verified this with the following C# code:

byte[] bytes = new byte[] { 0x5B, 0xB0, 0x05, 0x5B, 0xA9, 0xD9, 0xE1, 0x40 };
long asInt64 = BitConverter.ToInt64(bytes, 0);
double tr = BitConverter.Int64BitsToDouble(asInt64);
DateTime dt = DateTime.FromOADate(tr);

C++ code for working with this type of date is built into Windows. I don't know what you could use on other platforms.

2
  • 1
    This was the first thing I tried. I think I entered the bytes in the wrong order, assuming little endian representation. You can do the conversion directly: double tr = BitConverter.ToDouble(bytes, 0); Oct 31, 2022 at 5:03
  • Thanks! This gave such a headache, since its my first time working this extensively with hex and parsing. Very appreciated!
    – rosi97
    Oct 31, 2022 at 7:15
3

First, thanks for those who decided to close this question for no good reason whatsoever, forcing me to put an answer into comments first.

This is one of the two formats used by old Microsoft Excel versions to store dates and times. Both store the date as consecutive numbers with the start date either at Jan 1st 1900 or Jan 1st 1904. The 1904 seems to be based on the date format used internally by MacOS in 1984, making you wonder what genius preferred having two incompatible date formats to adding one line of code. The Jan 1st 1900 was probably assigned a value of 1.0 so that a value of 0.0 can be used to indicate "no date". There are 25 leap years from Jan 1st 1900 to Jan 1st 2000, because 1900 is a leap year. So the difference in days is 36500 + 25 leap days.

The number is stored as a 64 bit double precision floating point number. Start of Jan 1st 2000 is 40 e1 d5 c0 00 00 00 00. There is one bit sign, 11 bit biased exponent, and a 53 bit mantissa with the leading bit not stored. So sign = 0, biased exponent = 0x40e, mantissa = 11 d5 c0 00 00 00, with five bits in the highest byte.

The number 1.0 has a biased exponent of 0x3ff, so the number here has a real exponent of 0x40e - 0x3ff = 15, for numbers from 32768 to 65535. The highest 16 bits of the mantissa are the day. 5 bits = 17 from 0x11, 8 bits from 0xd5 = 211, 3 highest bits from 0xc0 = 6. Total 36526. So base 1st Jan 1900 = 1, add 100 x 365 days, plus 25 leap years.

One second is stored as 1.0 / 86400. There are 37 bits for fractions of days until 65536 days from 1900, around 2079. Until then the resolution is 2^37 / 86400 or about 1590728.6 per second.

But the algorithm is simple: Convert the date and time into number of days since Jan 1st 1900 and add one; calculate and store the result as 64 bit floating point. To add a day, add 1.0. To add a second, add 1.0 / 86400. To get days and seconds, take floor(x) to give days, and multiply the remainder by 86400 to get seconds.

1

You have run into one of the frustrations with parsing dates. Is 3/1/2000 March 1, or 3 January? The date format is ambiguous. Typically you resolve this using one of the methods below:

  • Ask someone. They might know because they already worked on it.

  • Guess. Yup, that's right. You inspect the dates in that file and see if you can identify an unambiguous date. If you see 12/4/YYYY you could assume the month is first.

    • Then confirm this fact with someone who knows more about this file.
    • Look for other date formats as well in the same time. If you also see YYYY/XX/XX your intelligent guess could still be wrong. Go back to point number one.
  • Consult the documentation for whoever is giving you this file.

Even if you use a proper date library, you still have the same problem. You need to tell that library which date format is being used.

Dates are notoriously difficult to deal with. The only way forward is to figure out which date format is being used, either by guesswork, communication with teammates, or consulting documentation.

And if you must guess, communicate this guess to other teammates, management, or write this down in the documentation for the code you are writing. It would be a good idea to note this guess as a risk for whatever you are building. Guessing the wrong format means the dates are wrong, which could affect downstream business processes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.