Wow, iPhoto’09 is even worse. Photo times are stored as decimal days since Jan 1, 2000 UTC. So, here’s what I used to get a human-readable time out of my little test database:
sqlite> select photoDate, datetime((photoDate + julianday(‘2000-01-01′) – 2440587.5)*86400.0,’unixepoch’,’localtime’) from SqPhotoInfo;
2583.90924768519|2007-01-27 15:49:19
1629.47916666667|2004-06-17 06:30:00
It actually only took me a few minutes to reverse engineer because I’ve noticed that whenever Apple stores a date as a float, it’s almost always Julian days or something related. And I’m used to working with Julian days from my years as an astronomer.
So days prior to Jan 1, 2000, are stored as negative numbers? (Or was someone assuming that no one would want to include scanned photos from past decades and assign them their actual dates?)
Peter Erwin Yes, negative. That’s what clued me into the 2000 epoch, because I’ve been gradually scanning my old photos from the 70s and 80s and adding them to iPhoto.