More on iPhotoMain.db / iPhotoAux.db - Date Formats!

There seems to be a dearth of information about the iPhoto SQLite3 database, so I am going to continue to post what I find out in hopes that it will help others. This is a follow up to my other posts about iPhotoMain.db SQLite3 database, which can be found here:

Additionally, I found the information I needed from the following blog posts on Punctuated Noise.

The key info I needed that came from the last post above was:

update SqGlobals set modificationDate = (julianday(\"now\") - 
julianday(\"2001-01-01 00:00:00\")) * 60 * 60 * 24;

From this I was able to determine how to ‘decode’ Apple’s floating point date representation. Not only does Apple use a floating point number to represent a date (it actually form a julian day), but they apparently have 2 different forms of it. UGH!

For those of you who just want the info, check this query out which selects records from the iPhotoAux.db table, SqPhotoInfoOther.

select primaryKey, 
archiveDate as rawArchiveDate, 
datetime((archiveDate / (60 * 60 * 24)) + julianday('2001-01-01 00:00:00')) 
   as archiveDate, 
originalPhotoDate as rawOriginalPhotoDate,
datetime(originalPhotoDate + julianday('2000-01-01 00:00:00')) 
   as originalPhotoDate, 
originalFilename
from SqPhotoInfoOther;

If you execute the following SQL with values from the SqPhotoInfoOther.archiveDate and SqPhotoInfoOther.orgiginalPhotoDate - you’ll get some nonsensical values back.

select datetime(335385037.045712) as archiveDate, datetime(4239.64527777778) 
as originalDate;

archiveDate: 913540-11228636-08 13:05:49
originalDate: -4701-07-04 03:29:12

From my Date Format used in iPhotoMain.db database post above, you can see I figured out the 2nd date (original photo date) a while ago… you just need to add the julian day for Jan 1st 2000 @ midnight to the floating point value, then use the datetime() function on the new value.

I tried this for the first date (archiveDate) and no luck: 920252-11228637-15 01:05:49. No improvement. When I came across the update statement above from the Punctuated Noise blog. I knew I had it!

For some strange reason you had to divide the floating point value by the number of seconds in a day (60 * 60 * 24 or 86,400) before adding the julian day for Jan 1st 2000 @ midnight. BINGO!

Now if some one could tell me why Apple, in there infinite wisdom, did it this way. I suspect its something to do with how the Core Data API handles time stamps fields versus date time fields as I have noticed that I have to adjust the archive date by -5 hrs (current offset from GMT for the Central Time Zone), that is the number is GMT, and I don’t have to do that for the original photo date…

Or may be it has something to do with iPhoto knows to store the archive date in GMT and then converts it to the local time zone when displaying it. But for the original photo date, the EXIF data from camera doesn’t have the time zone, so it stores it as it comes in.

Pity, it would be nice if both dates were stored in GMT for consistency.

1 year ago

Mac Code Monkey

More on iPhotoMain.db / iPhotoAux.db - Date Formats!