How do I retrieve the date from Evernote date_created timestamp field

[Cuchulain of Muirthemne 1]

Hi folks,

I querying the SQLite DB directly and have had some strange encounters with the timestamp fields. From what I can gather they are supposed to be based on the Unix Epoch of 1970-01-01

This timestamp; 736012.8334375 is 2016/02/18 21:00

but trying 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, 'unixepoch')

Just gives

"1970-01-01 02:21:58","1970-01-09 12:26:52","1970-01-09 12:26:52","1464340190",,"1970-01-09 12:26:52"

This Excel formula 

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

4/28/93 5:00 PM

Has anyone dealt with this before? Can they shed some light into this hole I'm in

Thanks

/C

[phils 220]

Please see the forum threads referenced below.  In the future, please post developer questions on StackOverflow and tag them with "Evernote" - developer support has moved from this forum to StackOverflow.

 

[JMichaelTX 4,117]

On 5/27/2016 at 10:24 AM, phils said:

Please see the forum threads referenced below.  In the future, please post developer questions on StackOverflow and tag them with "Evernote" - developer support has moved from this forum to StackOverflow.

I have the same question, but need a solution/functions that use JavaScript.

I have posted my question on StackOverflow here:

How Do I Convert Evernote Date/Time Using JavaScript?

[juneloon 3]

Found a SQLite solution for people who just want to use SQL: 

SELECT date((date_created * 86400) - 62135683200, 'unixepoch')  

Got a tip from https://stackoverflow.com/questions/37479884/retrieve-date-from-evernote-date-created-timestamp-field

Have to convert Evernote time to Unix time then can get Gregorian.

[JMichaelTX 4,117]

set epochTimeStr to "1207324439" -- MUST be text
set dateTimeStr to my epoch2DateTimeStr(epochTimeStr)

set asDate to date dateTimeStr

return asDate

on epoch2DateTimeStr(pEpochTimeStr)
	-- aka "Unix time" (seconds since 1/1/1970 00:00:00)
	-- Returns a Date/Time string in this format: "04/04/2008 10:53 AM"
	
	local cmdStr, dateTimeStr
	
	if (class of pEpochTimeStr ≠ text) then error "pEpochTimeStr MUST be an interger number formated of type TEXT"
	-- REF: (http://erikslab.com/2006/09/05/how-to-convert-an-epoch-time-to-a-meaningful-date-and-time/)
	-- CORRECTION by @JMichaelTX:  
	--	ADD ":S" to output seconds
	--	ADD " %p" to format to output AM/PM
	--	CHG ":H" to ":l" to output in 12-hour time
	
	set cmdStr to "date -r " & pEpochTimeStr & " \"+%m/%d/%Y %l:%M:%S %p\""
	set dateTimeStr to do shell script cmdStr
	
	return dateTimeStr
	
end epoch2DateTimeStr

-->For epochTimeStr = "1207324439":
-->dateTimeStr: "04/04/2008 10:53:59 AM"
-->asDate: date "Friday, April 4, 2008 at 10:53:59 AM"

EDIT:  2017-09-13  10:28 PM CT

• Completely replaced script  to fix two errors:

• ADD ":S" to output seconds

• ADD " %p" to format to output AM/PM

[phils 220]

I can't confirm that particular script will work but yes, Evernote stores its date/time values in epoch time - the number of seconds since 1/1/1970.

[JMichaelTX 4,117]

Thanks.

I made a correction to the script above, and I have now confirmed it using another system.