• Announcements

    • Shane D.

      Evernote Business Beta - Spaces   12/20/2017

      We're very excited to announce the public beta of an upcoming rework for Evernote Business! To learn more, go Here
    • Shane D.

      2018 Evernote Webinars   01/04/2018

      To kick off the new year, we're excited to announce our  updated schedule for our series of webinars! Please check the events calendar to see which one works best for you!
    • Shane D.

      Upcoming Forum Change Announcement   02/14/2018

      Hi All! You may be seeing some changes with overall organization and layout of the discussion forums. You can learn more by going to the 'Upcoming Forum Change Announcement' in the 'Community Announcements' sub-forum!  
  • 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

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
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
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

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