Pages

▼

Monday, February 21, 2011

Calculating Embedded OS X Times

I recently examined a Macintosh computer where I needed to look at Internet History.  The only installed browser was Safari, and the history was stored in /Users//Library/Safari/History.plist, an XML file with visit dates in recorded in epoch format.  An example of that time is "314335349.7". 


The tricky thing is realizing that not all so called "epoch" time is the same.  In a 'nix system, epoch time is defined as the number of seconds since 01/01/1970 00:00:00.  However, the Mac epoch time is defined as the number of seconds since 01/01/2001 00:00:00.  EDIT: Mac time is also known as "Mac Absolute Time."


Unix epoch time is a simple conversion in Linux.  A current time is a ten digit number that resembles "1298307237".  To convert that to human-readable date, simply:
$ date -d @1298307237
Mon Feb 21 08:53:57 PST 2011 
The date command defaults to calculating from 1970 and not 2001 as we need for our Mac time conversion.  To obtain a proper conversion, we need to tell the date command the starting point of the data calculation thusly:
$ date -d "2001-01-01 314335349.7 sec PST"
Sat Dec 18 03:22:29 PST 2010
Knowing the format, scripting the conversion should be relatively trivial.  I hope this helps someone.  I'll know I'll be back to this page often to remind myself of the conversion syntax!

EDIT:  When processing Mac databases, like those found on the iPhone, it is possible to convert the times using SQLite commands.  I determined the number of seconds since unixepoch time to Mac Absolute Time with "SELECT strftime('%s', '2001-01-01 00:00:00');" as 978307200 seconds.  This value can be added to Mac Absolute Time and then converted to local time with the SQLite datetime() function thusly: datetime(time_field + 978307200, 'unixepoch', 'localtime').
at

2 comments:

  1. Syclone0044November 28, 2012 at 4:55 PM

    Hey, this solved exactly what I needed to view the time on an SQLite timestamp. Strange that the iPhone/MacOSX does it differently, but you were like the first Google result, so thanks for the solution. Fortunately I had a unix shell but I wish someone had a nifty simple little online converter form.

    ReplyDelete
  2. MatHamlinDecember 30, 2012 at 9:42 PM

    I ended up running a "SELECT * FROM message" from the sqlite firefox plugin and exporting to csv, so I could bring the data into Excel.

    To show the date, I added a column and used this formula which check for Unix Epoch or Mac Epoch (my message had a mix).

    =IF(LEFT(C2,1)="1",SUM(C2/86400)+DATEVALUE("1/1/1970"),SUM(C2/86400)+DATEVALUE("1/1/2001"))

    I also created a column that showed the phone number since mine was split between "address" and "madrid_account". This formula check for blank in the address column then put in madrid_account if it is blank, address if it is not.

    =IF(ISBLANK(B4), AA4,B4)

    Now I'm going to start working on getting meaningful names for "flags" and "madrid_flags"

    ReplyDelete