+[[!tag tags/debian]]
+[[!tag tags/misc]]
+[[!tag tags/photography]]
+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](https://en.wikipedia.org/wiki/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](https://piwigo.org/) 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](https://arvadapress.com/stories/right-time-right-place-twice,78126?))
+nature photos, and during this trip she took thousands of photos using her
+relatively new
+[Nikon COOLPIX P900](https://www.nikonusa.com/en/nikon-products/product/compact-digital-cameras/coolpix-p900.html)
+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](https://piwigo.org/)
+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](https://www.sentex.ca/~mwandel/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:
+
+<pre>
+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;
+</pre>
+
+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:
+
+<pre>
+ cd /var/www/html/piwigo
+ for i in `cat /tmp/imagefiles`
+ do
+ echo $i
+ sudo -u www-data jhead -ta+15:05 $i
+ done
+</pre>
+
+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!
+