From: Bdale Garbee Date: Fri, 24 Jan 2020 09:09:33 +0000 (-0700) Subject: blog about photo creation time madness X-Git-Url: https://git.gag.com/?p=web%2Fgag.com;a=commitdiff_plain;h=adc437a64a169b9cbaf3b2a985edee359db7c9e1 blog about photo creation time madness --- diff --git a/bdale/blog/posts/Digital_Photo_Creation_Dates.mdwn b/bdale/blog/posts/Digital_Photo_Creation_Dates.mdwn new file mode 100644 index 0000000..dba0151 --- /dev/null +++ b/bdale/blog/posts/Digital_Photo_Creation_Dates.mdwn @@ -0,0 +1,142 @@ +[[!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 express 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... + +# 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. + +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 has, 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 fle piwigo_structure-mysql.sql +used in the installation process to set up the database tables tha 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 wer 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! + +