Parse 8 bytes to date time

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.