Evernote database f...
 
Notifications
Clear all

Evernote database files

16 Posts
5 Users
0 Likes
1,277 Views
Adam10541
(@adam10541)
Posts: 550
Honorable Member
Topic starter
 

Has anyone done work with Evernote database files before?

I'm seeing some useful information in the one of the .exb database files. To view it can be opened in various SQL tools or the extension changed to .db3 or whatever you need.

My question is around the date/time format used as the tools are reporting something weird.

The date created for a particular entry is 734508.144780093 (edited as excel was rounding the 'time' portion to 4 decimal places)

From looking at other entries it appears the 734508 is the date, and then I assume the 1448 is the time, however the time is not 24 format as this one would seem to indicate, as other times are listed as 9504 and various other numbers.

My first thought was Unix time, but it's not formatted correctly for that.

My next though is that the SQL tools while interpreting most of the data correctly maybe just don't know how to represent that field so it's a 'best effort' approach..

Any thoughts out there?

Edit I have posted a query on the Evernote forums but thought I'd ask here in case they don't want to divulge

 
Posted : 04/06/2014 1:49 pm
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
 

Hi Adam

Looking at the date it *could be* that the integer part is the number of days from 1/1/0000
and the fractional part *may be* the fraction of the day so .1448 = 86400 *.1448 = 32830.

To really bowl this out your would need more info though.

 
Posted : 04/06/2014 2:13 pm
Adam10541
(@adam10541)
Posts: 550
Honorable Member
Topic starter
 

Ahhh very interesting Paul, is there some sort of online calculator that can be used for that conversion or are you drawing on mathematical knowledge?

I'm ashamed to admit that maths baffles me at the best of times P

You could be on to something here, I did a quick google on how many years 734508 days is and it's 2011.2 years..

 
Posted : 04/06/2014 3:41 pm
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
 

No sorry (or rather I don't know of one) I knocked up a little program to do the conversion.

It should be easy enough to knock up something in excel to do the conversion.

There is a similar format for OLE automation mentioned in my article

http//sandersonforensics.com/forum/content.php?131-A-brief-history-of-time-stamps

I'll add a converter to RevEnge for this date as soon as I get a minute away from coding my new prog SQLite Recovery

 
Posted : 04/06/2014 3:47 pm
Adam10541
(@adam10541)
Posts: 550
Honorable Member
Topic starter
 

Reading the article now with a glass of wine…..may not be the best method for retention but I'll re-read tomorrow when I return to work P

Thanks Paul, much appreciated.

 
Posted : 04/06/2014 3:49 pm
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
 

My not be best for retention but I can't think of a better way )

 
Posted : 04/06/2014 3:50 pm
(@athulin)
Posts: 1156
Noble Member
 

is there some sort of online calculator that can be used for that conversion

You may want to check out the article on 'Julian day' on Wikipedia – the format that PauiSanderson suggests is (I think) the Rata Die system, or something closely related.

Note that you have to be very careful with Julian dates. Real Julian days begins at noon, but there are adjusted formats that begin at midnight, more in line with modern calender usage. You have to use the right formulas to get the right time conversion.

 
Posted : 04/06/2014 7:43 pm
(@angrybadger)
Posts: 164
Estimable Member
 

Paul is correct, the date part it to the left of the decimal point and the time is stored as a fraction of the day. (in UTC)

The date is stored as a "proleptic Gregorian ordinal"
(https://docs.python.org/2/library/datetime.html#datetime.date.fromordinal )

The following hacky Python code works against test values I pulled out of my Evernote database.


import datetime

def ordinalToGregorian( o )
l = int(o)
d = datetime.date.fromordinal( l )
t = datetime.timedelta( seconds = (( 24*60*60 ) * (o-l)) )
print d, t.__str__()

ordinalToGregorian( 735095.593981482 )

 
Posted : 04/06/2014 8:24 pm
Adam10541
(@adam10541)
Posts: 550
Honorable Member
Topic starter
 

These dates won't be relied upon too heavily, it was more me anticipating the question that would surely follow from my client when he sees the spreadsheet from the Evernote logs.

hmorgan

Nice one, thank you.

I have zero experience or knowledge of python and after installing and having a brief look at python this is going to hurt ?

Edit I just realised that excel was rounding off the number, the actual full date/time is 734508.144780093

 
Posted : 05/06/2014 5:48 am
Adam10541
(@adam10541)
Posts: 550
Honorable Member
Topic starter
 

Thank you to hmorgan and Paul for your help.

I wasn't able to get the python script working but that no doubt is due to my lack of experience with that software, however Paul wrote a nice little application that converts the date/time for me.

When work slows a little I will be revisiting the python idea for my own learning as it would appear to be a useful skill to have.

Thanks again gents, as always I am in awe of the knowledge and skill of some people here.

 
Posted : 06/06/2014 8:00 am
Page 1 / 2
Share: