• Announcements

    • gbarry

      Reach out to Evernote support on Twitter @EvernoteHelps   09/20/2016

      We've been leveling up our support across all channels. If you're active on Twitter, try your hand with our @evernotehelps feed. It's helmed by a crack team out of Austin, TX who are here to answer your questions. They're generally active from 7am-4pm CST.  https://twitter.com/evernotehelps  
  • 0
Cuchulain of Muirthemne

How do I retrieve the date from Evernote date_created timestamp field

Idea

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

Share this post


Link to post

6 replies to this idea

Recommended Posts

  • 0
phils    203

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.

 

Share this post


Link to post
  • 0
JMichaelTX    3,601
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?

Share this post


Link to post
  • 0
JMichaelTX    3,601
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

Share this post


Link to post
  • 0
phils    203

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now