SQLite timestamp field - converting to datetime

Question

EDIT

So with the help of MikeT this is what I have come up with to convert the Apple Cocoa Core Data timestamp values into Unix epoch values:

SELECT datetime(559951200 + 978307200, 'unixepoch', 'localtime');

Where 559951200 is my Apple Cocoa Core Data timestamp

Then, to get my localtime, I needed to add an additional parameter localtime to my datetime(...) function in SQLite.

---

I am revieving the sqlite database of my ScanSnap Home scanner and document manager for Mac.

It is located in ~/Library/Application Support/PFU/ScanSnap Home/Managed/ScanSnapHome.sqlite

The database has many tables with timestamp fields in it. I cannot convert those timestamp values to date/time.

For example: I have a date value that I can see in the user interface as 2018-09-30. That date value translates to 559951200 in the database (timestamp field).

Assuming the number is an epoch value, I try to convert it to a date value via https://www.epochconverter.com But it will give me September 30, 1987

What am I missing?

Answer 1

Your timestamp appears to be an Apple Cocoa Core Data timestamp.

By adding 978307200 it converts to an epoch value.

Thus 559951200 + 978307200 = 1538258400 which is epoch time for Saturday, 29 September 2018 22:00:00 - (GMT so local time may well be 30th).

Core Data has a start date of 2001/01/01 rather than 1970/01/01 and thus 978307200 is the number of seconds difference between the two.

Check out Cocoa Core Data Timestamp Converter