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  

Last edited by Adam10541 on Wed Jun 04, 2014 8:00 pm; edited 1 time in total

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 = 3:28:30.

To really bowl this out your would need more info though.
_________________
Paul Sanderson
SQLite Recovery - find and recover deleted sqlite dbs
sandersonforensics.com...e-Recovery
www.twitter.com/sandersonforens
www.facebook.com/recon...resoftware 

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

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

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

sandersonforensics.com...ime-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
_________________
Paul Sanderson
SQLite Recovery - find and recover deleted sqlite dbs
sandersonforensics.com...e-Recovery
www.twitter.com/sandersonforens
www.facebook.com/recon...resoftware 

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

Thanks Paul, much appreciated.  

My not be best for retention but I can't think of a better way
_________________
Paul Sanderson
SQLite Recovery - find and recover deleted sqlite dbs
sandersonforensics.com...e-Recovery
www.twitter.com/sandersonforens
www.facebook.com/recon...resoftware 

- Adam10541

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.