Decode date and time in a sequence of binary data

Question

INTRO

I'm trying to reverse engineer a binary data file containing sms messagges.
The file is named ems.idx4 and was created with a software named LG PhoneManager around 5 years ago as a backup archive of sms messagges for a LG mobile.
I don't know which language was used to write LG PhoneManager, but in the binary file I read strings like "CObTree", "CFolder", "CMessage": maybe this clue means nothing, maybe it suggests that Cobol/.net/whatever language was used.

PROBLEM

I decoded the entire structure of the binary file, which is quite plain tho.
The only part I couldn't decode is date and time of single messagges.
I identified the binary part where date and time are encoded and I got a few decoded examples (thanks to the content of the message).
Binary data in hex:

[0x10] D0 74 C4 FE 3F 42 E3 40 F1 64 [0x7] 2 [0x13] 1 [0x6] 6C [0x2] is 2007/12/25 some time after 23:58 GMT+1
[0x10] 2B 25 CA 19 2F 43 E3 40 F1 64 [0x7] 2 [0x13] 1 [0x6] 6C [0x2] is 2008/01/02 some time after 10:48 GMT+1
[0x10] AA C0 2C 6E 35 43 E3 40 F1 64 [0x7] 2 [0x13] 1 [0x6] 6C [0x2] is 2008/01/02 some time after 16:03 GMT+1
[0x10] EE 04 71 F2 B6 43 E3 40 F1 64 [0x7] 2 [0x13] 1 [0x6] 6C [0x2] is 2008/01/06 some time after 14:31 GMT+1
[0x10] 60 2C F9 45 4E 4F E3 40 F1 64 [0x7] 2 [0x13] 1 [0x6] 6C [0x2] is 2008/04/08 some time after 10:32 GMT+1
[0x10] 5D 84 01 14 74 64 E3 40 F1 64 [0x7] 2 [0x13] 1 [0x6] 6C [0x2] is 2008/11/11 some time after 14:53 GMT+1

where [0xN] means a sequence of N zeros.

Any idea?

UPDATE

Using this tool: http://www.digital-detective.co.uk/freetools/decode.asp
I realized it is Windows 64 bit OLE date / time format.
According to that tool:

D0 74 C4 FE 3F 42 E3 40 means exactly 26/12/2007 00:59

Any idea what the math behind this Windows 64 bit OLE date / time format?

I'm trying to do this exact same thing with Samsung PCStudio 3. Did you get anywhere with the format of the file because it looks to be quite similar. — Osmium USA, Jun 28 '16 at 16:13

score 5 · Answer 1 · edited Jun 17 '20 at 09:54

Apparently, OLE datetime is based on double values, where whole part is number of days since epoch, and fraction part is fraction of the day. Found some info in Time Format Conversion Made Easy in the , aptly named, section The Particular Nastiness of OLE Dates .

OLE Dates (VT_DATE) could have been a great: they specify days since the epoch (Dec 30, 1899), the fractional part giving the fraction of the day (e.g. 0.5 for noon). The floating point allows to trade of resolution near the epoch for long term calculations.

Negative values need to be split up into integer and fractional part: e.g. -2.5 means two days back and half a day forward from the epoch (i.e. the sign is applied to the integer part, but not the fractional part). This makes numeric errors especially troublesome, -2 means "two days back from the epoch", ending up at Dec 28, 1899. -1.9999999 means go back one day and almost an entire day forward again, ending you up just a bit before Dec 30, 1899 - almost two days apart.

The epoch was chosen to make Excel compatible with a bug in Lotus 1-2-3, leading to some inaccurate conversions in January and February 1900. For details, See link list below.

I didn't know this, thanks for a bit of lulz :)

Yeah, thanks! I think I found my way, will post the solution once done. Btw, OLE dates are such a mess! — nimiq, May 26 '13 at 18:27

nimiq · Answer 2 · 2013-05-26T21:28:33.650

Ok, I found my way!
The first 8 bytes after [0x10] are a OLE date in little endian hex.
I converted them to a regular datetime in python with:

import datetime
import math
from struct import unpack


def ole_date_bin_to_datetime(ole_date_bin):
    """
        Converts a OLE date from a binary 8 bytes little endian hex form to a datetime
    """
    #Conversion to OLE date float, where:
    # - integer part: days from epoch (1899/12/30 00:00) 
    # - decimal part: percentage of the day, where 0,5 is midday
    date_float = unpack('<d', ole_date_bin)[0]
    date_decimal, date_integer = math.modf(date_float)
    date_decimal = abs(date_decimal) 
    date_integer = int(date_integer)

    #Calculate the result
    res = datetime.datetime(1899, 12, 30) + datetime.timedelta(days=date_integer) #adding days to epoch
    res = res + datetime.timedelta(seconds = 86400*date_decimal) #adding percentage of the day
    return res


if __name__ == "__main__":
    print ole_date_bin_to_datetime('\xd0\x74\xc4\xfe\x3f\x42\xe3\x40')

are you sure about adding the percentage? it should be ok for positive values, but not sure about negative (given that remark negative values i mentioned in my answer). Tho in your case you probably won't have to deal with SMS messages older than 1899 :D — 0xea, May 26 '13 at 21:06
You are absolutely right. Just edited adding: date_decimal = abs(date_decimal) — nimiq, May 26 '13 at 21:29

Decode date and time in a sequence of binary data

2 Answers2