I have a program that stores data in a SQLite database in BLOB format.
Part of the data that is contained in this BLOB field is a date and time setting.
I'm trying to understand the format of this date and time.
I don't have access to the actual code of the program, I only can access the SQLite database to observe the changes made.
For example, if I set the program to have a date and time of 2019-05-09-0500PM, the hex code seems to be:
25 83 F5 03 A5 D6 80
I've played around with the date and time setting to see what gets stored in the database but I cannot figure out what kind of a timestamp this is.
Here are some sample settings from me observing each change I make to the date and time setting in the program:
Actual date and time HEX code
2019-05-09 - 0400 PM 25 83 F5 03 6E E8 00
2019-05-09 - 0500 PM 25 83 F5 03 A5 D6 80
2020-05-09 - 1100 PM 25 83 F5 04 EF 6D 80
2019-05-10 - 1200 AM 25 83 F6 00 00 00 00
2019-05-10 - 0300 AM 25 83 F6 00 A4 CB 80
2019-05-10 - 0400 AM 25 83 F6 00 DB BA 00
2019-05-10 - 0400 PM 25 83 F6 03 6E E8 00
2019-06-09 - 0400 PM 25 84 14 03 6E E8 00
2020-05-09 - 0400 PM 25 85 63 03 6E E8 00
Does anyone know what format this is in and how I could write my own new date and time to this BLOB field?
The program is running on Windows 10.
0x2583F503A5D680 - 0x2583F5036EE800, times 1 hour apart, is 3600000. There are 3600 seconds in an hour, and thus 3600000 milliseconds in an hour. So maybe the time's tracking milliseconds?
0x2583F6036EE800 - 0x2583F600DBBA00, times 12 hours apart, is 43200000. That also suggests milliseconds.
But... 0x2583F6036EE800 - 0x2583F5036EE800, times 24 hours apart, is 4294967296. However, there are 86400000 milliseconds in 24 hours. And 4294967296 isn't evenly divisible by 24. Curious. But... 0x2583F6 - 0x2583F5 is 1, and 0x036EE800 - 0x036EE800 is of course 0. So it looks like the first three bytes track date, and the last 4 count the number of milliseconds since the start of the day? 0x03A5D680 is 61200000, which is also 3600000 * 17, so it's what it should be for 5PM if that's the case. We're on to something!
0x258563 - 0x2583F5, days 1 year apart in 2019 and 2020, is 366. There's a leap year in 2020 so it makes sense that it's not 365, and helps confirm this theory about the format.
0x2583F5 is 2458613 days since the 0 point in this format. That's roughly 6735 years. The start date of the Julian Day counter is in 4713BC (Or 4714BC depending on what calendar you're using). That sounds like a match!
From Wikipedia:
Julian dates are expressed as a Julian day number with a decimal fraction added
So it looks like this is a binary encoding of the julian date that uses millseconds for the time of the day. I have no idea why the author isn't using REAL values to store it in the sqlite database, especially since that would make it usable by sqlite's date and time functions. Let's use them to verify:
sqlite> select julianday('2019-05-09 17:00:00');
2458613.208333333
Same day number as in the blob. So there you go. The format: