What timestamp format is this Sqlite column using?

Question

It turns out that the Notes app on an iPhone stores the notes in a sqlite database, and after a bunch of hacking, I was able to get this file onto my PC for backup and analysis. But I'm puzzled because the timestamp values are in this format, which I don't recognize:

-978184314.566524
394221085.203589
412276958.735525
412279753.951386

The first one could be some factory installed default note with a bogus timestamp. All the rest would have been made some time in the iPhone era, after 2010 say.

What format is this, and how to convert it to a human readable date and time?

looks like standard unix time expressed in fraction of a second, or you just missed to cut off those numbers after the dot while hacking this out of the iPhone... :) — benomatis
– benomatis, Commented Mar 26, 2016 at 20:19
I thought of that, but the numbers don't correspond to any timestamp when such notes could have been made. — Joshua Frank
– Joshua Frank, Commented Mar 26, 2016 at 20:40
yeah, I checked, 1982 is one of them, the other 2 are 1983... — benomatis
– benomatis, Commented Mar 26, 2016 at 20:41

CL. · Accepted Answer · 2016-03-26 21:52:00Z

1

Normal Unix timestamps are based on 1970. Apparently, these timestamps use 2000 instead:

> select strftime('%s', '2000-01-01');
946684800
> select datetime(-978184314.566524 + 946684800, 'unixepoch');
1969-01-01 10:08:05
> select datetime(394221085.203589 + 946684800, 'unixepoch');
2012-06-28 17:51:25
> select datetime(412276958.735525 + 946684800, 'unixepoch');
2013-01-23 17:22:38
> select datetime(412279753.951386 + 946684800, 'unixepoch');
2013-01-23 18:09:13

answered Mar 26, 2016 at 21:52

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joshua Frank Over a year ago

That's it! Thanks.

Collectives™ on Stack Overflow

What timestamp format is this Sqlite column using?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related