iPad internal date format

[Osxy]

I hope this is the right forum section for this, be kind mods if it's not .

I've been using Notes app for around 2 years and want to bulk export the stored notes for inclusion in a database backend for a web based CMS. The simplest method I could find, that didn't involve being forced to use a dfferent third party app with its own proprietary data format, or other equally loathsome exercises in dodgy marketing, was:

back up my iPad to my desktop running OSX

use iPad / iPhone Backup Extractor to export the raw data from the back up on my desktop,

then SQLite Database Browser to create a CSV file of the notes data.


[strikethrough] (sorry all, apparently being new to the forums I can't include links for the above apps, anyone interested can pm me for links)[/strikethrough]

The next step would be to simply filter out any data I don't need from the CSV file and upload to the the database. So far so groovy. All I really need from the data is the note contents and the creation date, and here I hit a snag - the creation date is stored as a string like this "308718186", which, for the example given, is displayed by Notes as "14 Oct 2010". On OSX I'd simply treat the digits as seconds passed since 1st January 1970 (aka the Unix epoch) to get the date, but far as I can tell this is not the case for iOS.

So what is it the internal date format for iOS, anyone?

 

[Osxy]

I've been digging about and come up with the answer to my own question - Cocoa's internal date format is :

the number of seconds since 1 January, 2001, at 00:00

Working with Date and Time in Cocoa (Part 1) ? Ole Begemann

So basically this is the OSX (and therefor iOS) epoch, while the Unix epoch starts on 1 January, 1970, at 00:00.

 

[Osxy]

For the heck of it - here's the answer in perl code (using stock vanilla perl):


first getting the difference between the 2 epochs:

#!/usr/bin/perl -w

use strict;
use Time::Local;


# timelocal() coverts human readable dates into the sum of the date in seconds 
# though it expects the date to be formatted as SECONDS,MINUTES,HOURS,DAY,MONTH,YEAR

my $unixEpoch=timelocal(0,0,0,1,1,1970); #00:00 1st January 1970
my $osxEpoch=timelocal(0,0,0,1,1,2001); #00:00 1st January 2001

my $epochOffset=($osxEpoch - $unixEpoch);

print $epochOffset,"\n";

The above ouputs "978307200", the 31 year difference between the 2 epoch times in seconds, which I can use to get a human readable date from the stored Notes creation date:

#!/usr/bin/perl -w

use strict;
use diagnostics-verbose;


my $cocoaDate=308718186;
my $unixOffset=978307200;
print scalar localtime($cocoaDate+$unixOffset);

the above script outputs the following date "Thu Oct 14 12:03:06 2010" which matches the date shown in the Notes interface on iPad.

So right about now I'm feeling pretty happy as I expected this to be much more tortuous to solve.


It's also worth stressing that following the steps outlined in my first post gives you access to all the data stored in your ipad without having to jailbreak it, and without forking out wads of cash for apps from companies trying to cash in on iPad data recovery issues caused by Apple's hermetic approach.

Set your data free!

 

[twerppoet]

Nice, glad you solved it; and thanks for letting us know how you did it.

 

[Osxy]

Thanks twerppoet, it wasn't documented (that I could find) so I thought it worthwhile writing down what I was doing.

And to anyone in general I can better document the process for actually getting stuff out of the encrypted back up file without using iTunes - which includes any application files, phtots or mp3s saved on your iPad - leave a comment so I can judge interest in this.