Date Format used in iPhotoMain.db database
Not sure how many of you know that iPhoto uses SQLite3 as its database engine. You can access the database files by right clicking on ~/username/Pictures/iPhoto Library and selecting Show Package Contents. Once you have displayed the package, the SQLite3 datbase files are:
- iPhotoMain.db
- iPhotoAux.db
- face.db
- face_blob.db
The main database file, as you might have guessed, is the iPhotoMain.db file. In Terminal, you can open the file by changing to the directory of iPhoto Library package, e.g.:
cd /Users/gskluzacek/Pictures/iPhoto\ Library
Then execute the following
sqlite3 iPhotoMain.db
One of the initial problems that I faced was when reading date fields, like the photoDate field on the sqPhotoInfo table. After some experimenting, I determined that the date is stored as a FLOAT which represents the number of days since midnight Jan 1st 2000. To convert this to a Julian Day value, you need to add the Julian Day of 1/1/2000 - then you can use the datetime() function to convert this to a more human readable format. For example:
select primaryKey,
datetime(photoDate + julianday('2000-01-01 00:00:00')) as photoDate,
archiveFilename from SqPhotoInfo order by archiveFilename desc;
Hopes this saves someone a couple of hours, I googled all over and couldn’t find this info.
8 months ago