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 @1298307237The 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:
Mon Feb 21 08:53:57 PST 2011
$ date -d "2001-01-01 314335349.7 sec PST"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!
Sat Dec 18 03:22:29 PST 2010
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
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.
ReplyDeleteI ended up running a "SELECT * FROM message" from the sqlite firefox plugin and exporting to csv, so I could bring the data into Excel.
ReplyDeleteTo 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"