I've looked at both [1] and [2] and I'm completely confused (and since the dbf file is a version 4 file, [1] should apply well). For one thing why does [1] state that the timestamp's date portion is the # of days since 1/1/4713 BC? That's just very puzzling. Secondly, assuming that it is the # of days since 4713 BC, I'm having some trouble with the value I am getting.
First off, my dbf file has a timestamp field which has an 8 byte long value. The actual date is 2000/8/16 17:21:41. In the dbf file, the 8 byte sequence is as follows 0x42ccb20e0340df00.
From [1], it says the first 4 bytes are for the date, and 2nd 4 bytes for the time. If the original byte sequence is actually little-endian (0x42ccb20e) then that should be 0x0eb2cc42 which comes to the value of 246598722. So date is 0x0eb2cc42 (246598722) and time is 0x00df4003 (14630915).
I must be missing something here or calculating something wrong. 246598722 is equivalent to 675612 years(assuming 1yr = 365 days, as adding leap years would confuse me..and shouldn't really be that much off).
From [2], I shouldn't use 01/01/4173bc as the basis but 12/31/1899 (well, 1/1/1900). But then, the date value I have isn't even in the range of what [2] shows.
Now if I take the actual value (2000/8/16) and use [1] and [2], I get the following:
method [1]: 2450501 days : (2000 - -4713) * 365 + (8 * 30) + 16 method [2]: 36756 days : [100 * 365 + 8 * 30 + 16] (over counting the # of days)
The dbf file isn't corrupted (otherwise, if I look at the timestamp in dBase, it'd crap out and display something crazy).
I've thought of using big-endian, but that makes even less sense as the values are even larger. I've even thought of the possibility that it's actually the # of seconds elapsed since either date, but that makes the values with even less sense. i.e. 246598722 = # of seconds elapsed (counting back from 2000/8/16) will make the base year as 1812. (calculations: 246898722 / (3600 * 365) = 187.8985, so 2000 - 187.8985 = 1812.1015)
Can someone point out where I'm doing this wrong?
Thanks!
[1] - https://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm [2] - Convert dBase Timestamp
I've finally found the answer thanks to [3].
Basically, the timestamp 8 byte sequence is used as a whole with the following notes:
It's stored in big-endian.
The last byte is not used.
It's a Julian Day Number.
So in my case, it's 0x42ccb20e0340df00 and truncating the last byte, I get 0x42ccb20e0340df.
Then the following python code gets the correct info:
import datetime
base = 0x42cc418ba99a00
frm_date = int('42ccb20e0340df', 16)
final_ts = (frm_date - base) / 500
final_date = datetime.datetime.utcfromtimestamp(final_ts)
which outputs 2000-8-16 17:21:41 and some milliseconds, which I just ignore.
So I'm guessing the theory is that the above code moves the 'base' date to 1970/1/1 from 1/1/1, which helps since utcfromtimestamp() doesn't work with any value prior to 1970/1/1.
My confusion stems from the fact it doesn't use 4713BC as the base year, instead it uses 1/1/1, though I'm still trying to figure out how to get the value 0x42cc418ba99a00 for 1970/1/1.