Search code examples
sqlitedatetimeepochevernote

Retrieve date from Evernote date_created timestamp field

Does anyone know on what basetime Evernote calculates datetime?

I need to directly deal with the notes table in the Evernote SQLite DB and the documentation refers people to the SQLite manual https://www.sqlite.org/lang_datefunc.html

This stored time 736012.8334375 should yield 2016/02/18 21:00

I've tried multiple variants such as 

select datetime(((((736012.8334375)*1000/60)/60)/24), 'unixepoch'), datetime(((736012.8334375)), 'unixepoch'), datetime(736012.8334375, 'unixepoch'), strftime('%s', 'now'), strftime('7736012.8334375', 'unixepoch'), datetime((736012.8334375 *1000), 'unixepoch')

giving

"1970-01-01 23:39:46","1970-01-09 12:26:52","1970-01-09 12:26:52","1464341058",,"1993-04-28 16:00:33"

This Excel formula 

=((((736012.8334375)*1000/60)/60)/24)+DATE(1970,1,1)+(1/24)

gets closer with

4/28/93 5:00 PM

but still a bit out.

What am I doing wrong?

Solution

  • Here's the formula I arrived at for determining the real date from Evernote's dates:

    unixTime = (EvernoteTime * 86400) - 62135683200

    I've tested this with a few time zones by exporting data from the Evernote app to html and hand-verifying the times match.

    I'm not sure where the 62135683200 comes from. It's not quite the difference between unix epoch and year zero, but after arriving at the correct value I stopped trying to figure it out.