I'm trying to interface with Things 3's SQLite database and have come across a timestamp that I can't find a standard for.
Some of the values stored and their corresponding dates: 131141760 - 1/1/2001 131731584 - 1/1/2010 132059264 - 1/1/2015 132649088 - 1/1/2024
Oddly, other data in the table is stored as UNIX timestamps (i.e. 1724349077.57524) so I'm not sure why this data is different. Maybe they are using some non-standard epoch?
I've tried offsetting the values to align them with a standard epoch but their offsets all vary. If it's helpful, I can provide the database or additional dates. I reached out to Things support and they just told me the values were UNIX, but the dates are way off and they don't align with UNIX or Cocoa Core Data conversions.
The value you have seems to be a bitwise encoding of year / month / day. Based on the examples you've provided, they can be decoded like this:
with x(value) as (
values (131141760), (131731584), (132059264), (132649088), (132653184), (131150208), (132609920)
)
select
value,
(value / 65536) as year,
((value / 4096) % 16) as month,
((value / 128) % 32) as day
-- or using bitwise operators
-- value >> 16 as year,
-- ((value >> 12) & 15) as month,
-- ((value >> 7) & 31) as day
from x;
Output:
value,year,month,day
131141760,2001,1,1
131731584,2010,1,1
132059264,2015,1,1
132649088,2024,1,1
132653184,2024,2,1
131150208,2001,3,3
132609920,2023,7,15
Explanation:
132609920 =
111111001110111011110000000 in binary =
11111100111 0111 01111 0000000
^ year ^ mo ^ day
= 2023 7 15