Fixing Date import errors in F-Spot

I went to DEFCON XVII. Of course I took a camera, but screwed up and didn't set the date/time. I snapped a couple of hundred pictures, imported them into F-Spot and later realized that they were dated in 2005! Doh! Here is how I reset them to the correct values.

Here's my setup:
Ubuntu 9.04, x86_64 with f-spot

Step 1: Is to backup the photos and the f-spot database. Don't skip this step - losing your photos is not a happy ending, spend the time it takes to make a backup.
To backup the database, copy ~/.gnome2/f-spot/photos.db

Step 2: Make sure you've got exiftool & sqlite3 packages installed. You can install them via:
sudo apt-get install sqlite3
sudo apt-get install exif libimage-exiftool-perl

Step 3: Use F-Spot to highlight the files you want to fix, Right-click and "Copy Photo Location". Paste this list into your favourite editor. I used vi.

Step 4: Turn the file list into a script file. First change that pasted text into a series of commands by telling vi:
Substitute CR for a space:
:%s/ /^M/g
Then insert the move command at the front of every line:
:%s/^/mv /
Then append the destination of the move to every line:
:%s/$/ scratchdir/
Finally, save this as getFilesToFix.bash
:w getFilesToFix.bash
And quit

Step 5: Shutdown F-Spot. We'll be playing with the database so lets not confuse F-Spot.

Step 6: Create the scratch directory and run the script you created:
mkdir scratchdir

Step 7: You need to calculate date and time offset from the set of pictures to the date and time that should have been recorded. If you made multiple imports with different, incorrect dates you'll have to repeat this procedure for the pictures in each incorrect import set.
In my case I found a photo that showed my watch - bonus! So I had the date from the photo and the date/time from my watch:
Photo: 2005.07.04 19:23:54
Watch: 2009.07.30 18:23:xx (I couldn't read the seconds)
So, that meant I needed to add 4:0:26 0:0:0 (YYYY:MM:DD HHMMSS)
- The hour apparent difference between 19: & 18: is because the photos were taken in PDT and I am processing them in MDT.
The exiftool command to do this is:
exiftool -r -ext jpg -AllDates+="4:0:26 0:0:0" scratchdir
but we're going to put that into our script so don't worry.

The actual script that I used for this is:

exiftool -r -ext jpg -AllDates+="4:0:26 0:0:0" ${PWD}
if [[ -w photos.commands ]]; then
   rm photos.commands
if [[ -w photo_versions.commands ]]; then
   rm photo_versions.commands
#set -x
find ${PWD} -iname '*.jpg' -print |
while read FILENAME; do
   ls -l ${FILENAME}
   EXIFDATETIME=$(exiftool -ext jpg -TAGS -CreateDate ${FILENAME} | sed -e 's/.*: \(200[0-9]:.*\)/\1/')
   DATETIMESTR=$(echo ${EXIFDATETIME} | sed -e 's/200[0-9]:\([0-9][0-9]\):\([0-9][0-9]\) \([0-9][0-9]\):\([0-9][0-9]\):\([0-9][0-9]\)/2009\/\1\/\2 \3\4.\5/')
   TARGETDIR=$(echo ${DATETIMESTR} | sed -e 's/^\([^ ]*\) .*/\1/')
   TIMESTAMP=$(echo ${DATETIMESTR} | sed -e 's/[/ ]//g')
   EXIFDATETIME=$(echo ${EXIFDATETIME} |  sed -e 's/:/-/' -e 's/:/-/')

   if [[ ! -d ${TARGETDIR} ]]; then
      if ! mkdirhier ${TARGETDIR}; then
         echo "Error creating ${TARGETDIR}" >&2

#  echo "mv ${FILENAME} ${TARGETDIR}/$(basename ${FILENAME})"
#  echo "touch -t ${TIMESTAMP} ${TARGETDIR}/$(basename ${FILENAME})"

   INTEGERTIME=$(date --date="${EXIFDATETIME}" +%s)

   echo "update photos         set uri = \"file:///home/jardine/Photos/${TARGETFILESPEC}\", time=${INTEGERTIME} where uri LIKE \"%$(basename ${FILENAME})\";" >> photos.commands
   echo "update photo_versions set uri = \"file:///home/jardine/Photos/${TARGETFILESPEC}\" where uri LIKE \"%$(basename ${FILENAME})\";" >> photo_versions.commands
#  break
set -x

Copy this script, call it fixdates.bash and put it into your scratchdir
Change the hard-coded path to the top of your Photo directory.

Step 8:
cd scratchdir
cat photos.commands | sqlite3 ~/.gnome2/f-spot/photos.db
cat photo_versions.commands | sqlite3 ~/.gnome2/f-spot/photos.db

Step 9: Look in scratchdir, you'll have one or more directories with the year that the photos were actually taken, in my case 2009. You need to merge the contents of this directory (2009) with your Photos/2009 directory. I used the file manager to do it but use whatever works for you. The key here is to merge the modified files in.

That's it! I highly recommend that you test this procedure on your setup before doing it for real - you may have a different configuration than I did.