I learned something new yesterday, that probably shouldn't have shocked me as much as it did. For legacy reasons, the "creation time" in the Exif metadata attached to digital camera pictures is not expressed in absolute time, but rather in some arbitrary expression of "local" time! This caused me to spend a long evening learning how to twiddle Exif data, and then how to convince Piwigo to use the updated metadata. In case I or someone else need to do this in the future, it seems worth taking the time to document what I learned and what I did to "make things right".

The reason photo creation time matters to me is that my wife Karen and I are currently in the midst of creating a "best of" subset of photos taken on our recently concluded family expedition to Antarctica and Argentina. Karen loves taking (sometimes award-winning) nature photos, and during this trip she took thousands of photos using her relatively new Nikon COOLPIX P900 camera. At the same time, both of us and our kids also took many photos using the cameras built into our respective Android phones. To build our "best of" list, we wanted to be able to pick and choose from the complete set of photos taken, so I started by uploading all of them to the Piwigo instance I host on a virtual machine on behalf of the family, where we assigned a new tag for the subset and started to pick photos to include.

Unfortunately, to our dismay, we noted that all the photos taken on the P900 weren't aligning correctly in the time-line. This was completely unexpected, since one of the features of the P900 is that it includes a GPS chip and adds geo-tags to every photo taken, including a GPS time stamp.

Background

We've grown accustomed to the idea that our phones always know the correct time due to their behavior on the mobile networks around the world. And for most of us, the camera in our phone is probably the best camera we own. Naively, my wife and I assumed the GPS time stamps on the photos taken by the P900 would allow it to behave similarly and all our photos would just automatically align in time... but that's not how it worked out!

The GPS time stamp implemented by Nikon is included as an Exif extension separate from the "creation time", which is expressed in the local time known by the camera. While my tiny little mind revolts at this and thinks all digital photos should just have a GPS-derived UTC creation time whenever possible... after thinking about it for a while, I think I understand how we got here.

In the early days of Exif, most photos were taken using chemical processes and any associated metadata was created and added manually after the photo existed. That's probably why there are separate tags for creation time and digitization time, for example. As cameras went digital and got clocks, it became common to expect the photographer to set the date and time in their camera, and of course most people would choose the local time since that's what they knew.

With the advent of GPS chips in cameras, the hardware now has access to an outstanding source of "absolute time". But the Nikon guys aren't actually using that directly to set image creation time. Instead, they still assume the photographer is going to manually set the local time, but added a function buried in one of the setup menus to allow a one-time set of the camera's clock from GPS satellite data.

So, what my wife needs to do in the future is remember at the start of any photo shooting period where time sync of her photos with those of others is important, she needs to make sure her camera's time is correctly set, taking advantage of the function that allows here to set the local time from the GPS time. But of course, that only helps future photos...

How I fixed the problem

So the problem in front of me was several thousand images taken with the camera's clock "off" by 15 hours and 5 minutes. We figured that out by a combinaton of noting the amount the camera's clock skewed by when we used the GPS function to set the clock, then noticing that we still had to account for the time zone to make everything line up right. As far as I can tell, 12 hours of that was due to AM vs PM confusion when my wife originally set the time by hand, less 1 hour of daylight savings time not accounted for, plus 4 time zones from home to where the photos were taken. And the remaining 5 minutes probably amount to some combination of imprecision when the clock was originally set by hand, and drift of the camera's clock in the many months since then.

I thought briefly about hacking Piwigo to use the GPS time stamps, but quickly realized that wouldn't actually solve the problem, since they're in UTC and the pictures from our phone cameras were all using local time. There's probably a solution lurking there somewhere, but just fixing up the times in the photo files that were wrong seemed like an easier path forward.

A Google search or two later, and I found jhead, which fortunately was already packaged for Debian. It makes changing Exif timestamps of an on-disk Jpeg image file really easy. Highly recommended!

Compounding my problem was that my wife had already spent many hours tagging her photos in the Piwigo web GUI, so it really seemed necessary to fix the images "in place" on the Piwigo server. The first problem with that is that as you upload photos to the server, they are assigned unique filenames on disk based on the upload date and time plus a random hash, and the original filename becomes just an element of metadata in the Piwigo database. Piwigo scans the Exif data at image import time and stuffs the database with a number of useful values from there, including the image creation time that is fundamental to aligning images taken by different cameras on a timeline.

I could find no Piwigo interface to easily extract the on-disk filenames for a given set of photos, so I ended up playing with the underlying database directly. The Piwigo source tree contains a file piwigo_structure-mysql.sql used in the installation process to set up the database tables that served as a handy reference for figuring out the database schema. Looking at the piwigo_categories table, I learned that the "folder" I had uploaded all of the raw photos from my wife's camera to was category 109. After a couple hours of re-learning mysql/mariadb query semantics and just trying things against the database, this is the command that gave me the list of all the files I wanted:

select piwigo_images.path into outfile '/tmp/imagefiles' from piwigo_image_category, piwigo_images where piwigo_image_category.category_id=109 and piwigo_images.date_creation >= '2019-12-14' and piwigo_image_category.image_id=piwigo_images.id;

That gave me a list of the on-disk file paths (relative to the Piwigo installation root) of images uploaded from my wife's camera since the start of this trip in a file. A trivial shell script loop using that list of paths quickly followed:

        cd /var/www/html/piwigo
        for i in `cat /tmp/imagefiles`
        do
                echo $i
                sudo -u www-data jhead -ta+15:05 $i
        done

At this point, all the files on disk were updated, as a little quick checking with exif and exiv2 at the command line confirmed. But my second problem was figuring out how to get Piwigo to notice and incorporate the changes. That turned out to be easier than I thought! Using the admin interface to go into the photos batch manager, I was able to select all the photos in the folder we upload raw pictures from Karen's camera to that were taken in the relevant date range (which I expressed as taken:2019-12-14..2021), then selected all photos in the resulting set, and performed action "synchronize metadata". All the selected image files were rescanned, the database got updated...

Voila! Happy wife!