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 (file)
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:
+
+<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 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!
+
+