3 [[!tag tags/photography]]
4 I learned something new yesterday, that probably shouldn't have shocked me
5 as much as it did. For legacy reasons, the "creation time" in the
6 [Exif](https://en.wikipedia.org/wiki/Exif) metadata attached to digital
7 camera pictures is not expressed in absolute time, but rather in some
8 arbitrary expression of "local" time! This caused me to spend a long evening
9 learning how to twiddle Exif data, and then how to convince
10 [Piwigo](https://piwigo.org/) to use the updated metadata. In case I or
11 someone else need to do this in the future, it seems worth taking the time
12 to document what I learned and what I did to "make things right".
14 The reason photo creation time matters to me is that my wife Karen and I are
15 currently in the midst of creating a "best of" subset of photos taken on our
16 recently concluded family expedition to Antarctica and Argentina. Karen
17 loves taking (sometimes
18 [award-winning](https://arvadapress.com/stories/right-time-right-place-twice,78126?))
19 nature photos, and during this trip she took thousands of photos using her
21 [Nikon COOLPIX P900](https://www.nikonusa.com/en/nikon-products/product/compact-digital-cameras/coolpix-p900.html)
22 camera. At the same time, both of us and our kids also took many photos
23 using the cameras built into our respective Android phones. To build our
25 wanted to be able to pick and choose from the complete set of photos taken,
26 so I started by uploading all of them to the [Piwigo](https://piwigo.org/)
27 instance I host on a virtual machine on behalf of the family, where we
28 assigned a new tag for the subset and started to pick photos to include.
30 Unfortunately, to our dismay, we noted that all the photos taken on the P900
31 weren't aligning correctly in the time-line. This was completely unexpected,
32 since one of the features of the P900 is that it includes a GPS chip and adds
33 geo-tags to every photo taken, including a GPS time stamp.
37 We've grown accustomed to the idea that our phones always know the correct
38 time due to their behavior on the mobile networks around the world. And for
39 most of us, the camera in our phone is probably the best camera we own.
40 Naively, my wife and I assumed the GPS time stamps on the photos taken by
41 the P900 would allow it to behave similarly and all our photos would just
42 automatically align in time... but that's not how it worked out!
44 The GPS time stamp implemented by Nikon is included as an Exif
45 extension separate from the "creation time", which is expressed in the
46 local time known by the camera. While my tiny little mind revolts at this
47 and thinks all digital photos should just have a GPS-derived UTC creation
48 time whenever possible... after thinking about it for a while, I think I
49 understand how we got here.
51 In the early days of Exif, most photos were
52 taken using chemical processes and any associated metadata was created and
53 added manually after the photo existed. That's probably why there are
54 separate tags for creation time and digitization time, for example. As
55 cameras went digital and got clocks, it became common to expect the
56 photographer to set the date and time in their camera, and of course most
57 people would choose the local time since that's what they knew.
59 With the advent of GPS chips in cameras, the hardware now has access to an
60 outstanding source of "absolute time". But the Nikon guys aren't actually
61 using that directly to set image creation time. Instead, they still assume
62 the photographer is going to manually set the local time, but added a
63 function buried in one of the setup menus to allow a one-time set of the
64 camera's clock from GPS satellite data.
66 So, what my wife needs to do in the future is remember at the start of any
67 photo shooting period where time sync of her photos with those of others is
68 important, she needs to make sure her camera's time is correctly set, taking
69 advantage of the function that allows here to set the local time from the
70 GPS time. But of course, that only helps future photos...
72 # How I fixed the problem
74 So the problem in front of me was several thousand images taken with the
75 camera's clock "off" by 15 hours and 5 minutes. We figured that out by a
76 combinaton of noting the amount the camera's clock skewed by when we used
77 the GPS function to set the clock, then noticing that we still had to account
78 for the time zone to make everything line up right. As far as I can tell, 12
79 hours of that was due to AM vs PM confusion when my wife originally set
80 the time by hand, less 1 hour of daylight savings time not accounted for,
81 plus 4 time zones from home to where the photos were taken. And the remaining
82 5 minutes probably amount to some combination of imprecision when the clock
83 was originally set by hand, and drift of the camera's clock in the many
86 I thought briefly about hacking Piwigo to use the GPS time stamps, but quickly
87 realized that wouldn't actually solve the problem, since they're in UTC and
88 the pictures from our phone cameras were all using local time. There's
89 probably a solution lurking there somewhere, but just fixing up the times in
90 the photo files that were wrong seemed like an easier path forward.
92 A Google search or two later, and I found
93 [jhead](https://www.sentex.ca/~mwandel/jhead/),
94 which fortunately was already packaged for Debian. It makes changing Exif
95 timestamps of an on-disk Jpeg image file really easy. Highly recommended!
97 Compounding my problem was that my wife had already spent many hours tagging
98 her photos in the Piwigo web GUI, so it really seemed necessary to fix the
99 images "in place" on the Piwigo server. The first problem with that is that
100 as you upload photos to the server, they are assigned unique filenames on
101 disk based on the upload date and time plus a random hash, and the original
102 filename becomes just an element of metadata in the Piwigo database. Piwigo
103 scans the Exif data at image import time and stuffs the database with a
104 number of useful values from there, including the image creation time that is
105 fundamental to aligning images taken by different cameras on a timeline.
107 I could find no Piwigo interface to easily extract the on-disk filenames for
108 a given set of photos, so I ended up playing with the underlying database
109 directly. The Piwigo source tree contains a file piwigo_structure-mysql.sql
110 used in the installation process to set up the database tables that served as
111 a handy reference for figuring out the database schema. Looking at the
112 piwigo_categories table, I learned that the "folder" I had uploaded all of
113 the raw photos from my wife's camera to was category 109. After a couple
114 hours of re-learning mysql/mariadb query semantics and just trying things
115 against the database, this is the command that gave me the list of all the
119 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;
122 That gave me a list of the on-disk file paths (relative to the Piwigo
123 installation root) of images uploaded from my wife's camera since the start
124 of this trip in a file. A trivial shell script loop using that list of
125 paths quickly followed:
128 cd /var/www/html/piwigo
129 for i in `cat /tmp/imagefiles`
132 sudo -u www-data jhead -ta+15:05 $i
136 At this point, all the files on disk were updated, as a little quick checking
137 with exif and exiv2 at the command line confirmed. But my second problem
138 was figuring out how to get Piwigo to notice and incorporate the changes. That
139 turned out to be easier than I thought! Using the admin interface to go into
140 the photos batch manager, I was able to select all the photos in the folder
141 we upload raw pictures from Karen's camera to that were taken in the relevant
142 date range (which I expressed as taken:2019-12-14..2021), then selected all
143 photos in the resulting set, and performed action "synchronize metadata". All
144 the selected image files were rescanned, the database got updated...
projects / web / gag.com / blob