Metadata: Prefer SubSecDateTimeOriginal and SubSecCreateDate timestamps

[danielhoherd]

What does not work as described in the documentation?

• Photo detail views have incorrect datetimes in some circumstances
• Chronological sort is incorrect in some circumstances

When viewing collections of images, timezones are not considered when sorting photos. Photos from two separate cameras set to the same objective time but with a different timezone will not correctly sort chronologically. It appears that the location of the photo may be interfering with correct interpretation of the time.

Photo details views show incorrect TZ and time when the TZ of the camera body does not match the photo geolocation.

How can we reproduce it?

Steps to reproduce the behavior:

1. Set your DSLR to GMT.
2. Take a photo.
3. Geotag this photo into a non-GMT timezone.
4. Notice the photoprism miscalculates the date as being taken within the geotagged timzeone, not what is shown in an exif tag that contains the TZ.

What behavior do you expect?

I expect photos that include the TZ in their exif data to be shown with correct times and sorted correctly among other files from different TZs.

What could be the cause of your problem?

It looks like there may be multiple things going on. The DB does not appear to store TZ data, although there is a photos.taken_at_local column. This column appears to be the result of some changes that take into account the location of the photo and the photo exif data. For example, the following photo shows 10:01 PM EDT.

The photo was taken in EST, but clearly it is not 10pm EDT since it's daytime. The details view of that photo does indeed show that it is in America/New_York TZ.

However, the camera that took that photo was set to GMT (because who wants to mess with TZ's every time you travel?) We can see in the exif data that it was taken at 10:01 GMT (+00:00):

$ exiftool -s -SubSecDateTimeOriginal -SubSecCreateDate -DateTimeOriginal -CreateDate 20220511-22-01-32_455c68.dng
SubSecDateTimeOriginal          : 2022:05:11 22:01:32.74+00:00
SubSecCreateDate                : 2022:05:11 22:01:32.74+00:00
DateTimeOriginal                : 2022:05:11 22:01:32
CreateDate                      : 2022:05:11 22:01:32

So my suspicion is that the import of photo metadata into Photoprism is ignoring fields that have more rich datetime data that includes the TZ. These fields are less ubiquitous than the TZ naive fields, but they are quite common in modern cameras, and if the TZ data is included in the exif then it should certainly be used. This is especially important for people who travel because they often forget to change the TZ on their camera, and end up shooting a bunch of photos with the clock set to the wrong timezone. If TZ data is available in the file and is used, then photoprism can gracefully deal with the photographer's mistake. If TZ data from the file is ignored, then we have chronological sort problems and incorrect metadata showing up in photo detail views.

Can you provide us with example files for testing, error logs, or screenshots?

I can provide sample files if asked to, but I don't think I have anything especially rare that you couldn't create by just using exiftool to change the dates on a file or using a DSLR with its clock set to another TZ.

Which software versions do you use?

• photoprism/photoprism:latest bc710aa81b1e (Build 220302-0059f429-Linux-AMD64)
• mariadb:10.6 3a25dc0f1777
• Ubuntu 22.04

On what kind of device is PhotoPrism installed?

• Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz (as reported by lscpu)
• 49257296 memory (as reported by free)
• SanDisk SSD PLUS 1000GB (as reported by lsblk -do NAME,SIZE,MODEL)

[lastzero]

Support for SubSec timestamps was added recently. Read priority has been increased so that they are used first. Might be all that's needed! Please test 👍

[danielhoherd]

@lastzero I just tested this on photoprism/photoprism@sha256:299ea4b4862e9e0385856dcdb75be0b82aff9f3aa4e65139d3342df33246ed2e created at 2022-05-17T01:56:40.667145444Z, and sadly the new build does not fix the problem.

• Re-scanned the whole db with "complete rescan": no change in the dates shown. Images still show as being taken in the geotagged TZ.
• Added new files with GMT TZ in the DSLR. Same problem as originally described.

I looked in the database and taken_at and taken_at_local appear to be the same as they were before. time_zone shows the geotagged location, not the TZ from the file.

MariaDB [photoprism]> select photos.time_zone, photos.taken_at_local, photos.taken_at from photos
    -> inner join files on photos.id=files.photo_id
    -> where files.file_name = 'personal/2021/12/06/20211206-03-29-10_493FjF.dng' \G
*************************** 1. row ***************************
     time_zone: America/Los_Angeles
taken_at_local: 2021-12-06 03:29:10
      taken_at: 2021-12-06 11:29:10
1 row in set (0.001 sec)

$ exiftool -s personal/2021/12/06/20211206-03-29-10_493FjF.dng | grep 2021:12:06
DateCreated                     : 2021:12:06 03:29:10.20Z
DateTimeOriginal                : 2021:12:06 03:29:10
CreateDate                      : 2021:12:06 03:29:10
SubSecCreateDate                : 2021:12:06 03:29:10.2+00:00
SubSecDateTimeOriginal          : 2021:12:06 03:29:10.2+00:00

[lastzero]

Your initial example showed time offsets +00:00, not time zones. Now your example shows a time zone, Z. However, reading the fields you mentioned first, leads to the DateCreated field being ignored. We can't read all fields and mix the information in creative ways.

[danielhoherd]

@lastzero sorry, I've been using TZ and offset interchangeably. The original example didn't include the DateCreated field, which is why the Z was not shown. The file I used in the original example (20220511-22-01-32_455c68.dng) does show the TZ for DateCreated and an offset for CreateDate, just like the file in my prior comment (20211206-03-29-10_493FjF.dng) does. These two photos were taken with the same camera body and same TZ settings.

[danielhoherd]

Looking more at the exiftool side of this, exiftool does provide a composite TimeZone field via custom config. After adding those contents to my ~/.ExifTool_config I now see my DSLR with GMT offset, and my iPhone with my local timezone offset. Here are the two files I used in examples above, and another iPhone file for comparison:

$ exiftool -s -Model -DateCreated -SubSecDateTimeOriginal -SubSecCreateDate -DateTimeOriginal -CreateDate -TimeZone 2022/05/11/20220511-22-01-32_455c68.dng 2021/12/06/20211206-03-29-10_493FjF.dng 2022/04/26/20220426-08-16-18_235Pil.heic
======== 2022/05/11/20220511-22-01-32_455c68.dng
Model                           : NIKON D850
DateCreated                     : 2022:05:11 22:01:32.74Z
SubSecDateTimeOriginal          : 2022:05:11 22:01:32.74+00:00
SubSecCreateDate                : 2022:05:11 22:01:32.74+00:00
DateTimeOriginal                : 2022:05:11 22:01:32
CreateDate                      : 2022:05:11 22:01:32
TimeZone                        : +00:00
======== 2021/12/06/20211206-03-29-10_493FjF.dng
Model                           : NIKON D850
DateCreated                     : 2021:12:06 03:29:10.20Z
SubSecDateTimeOriginal          : 2021:12:06 03:29:10.2+00:00
SubSecCreateDate                : 2021:12:06 03:29:10.2+00:00
DateTimeOriginal                : 2021:12:06 03:29:10
CreateDate                      : 2021:12:06 03:29:10
TimeZone                        : +00:00
======== 2022/04/26/20220426-08-16-18_235Pil.heic
Model                           : iPhone 11 Pro
SubSecDateTimeOriginal          : 2022:04:26 08:16:18.235-07:00
SubSecCreateDate                : 2022:04:26 08:16:18.235-07:00
DateTimeOriginal                : 2022:04:26 08:16:18
CreateDate                      : 2022:04:26 08:16:18
TimeZone                        : -07:00
    3 image files read

So it is pretty simple to coax exiftool into providing the bare timezone from file metadata. With these pieces, it should be possible to add TZ metadata contained within the file to photoprism.

[lastzero]

DateCreated is an XMP Field, DatesTimes in Exif for Images are not UTC but local time by default. Main exception are QuickTime and MP4 videos, which default to UTC. Since when does Nikon create DNG files with XMP Metadata? Writing this to XMP sidecar files instead should work for you. Note that even though Exiftool calls -7 a time zone, it's a time offset really.

[danielhoherd]

AFAIK Nikon does not create DNG files. These were run through Adobe DNG Converter, and manually geotagged. Nikon does support TimeZone as a default property though:

$ exiftool -s -G:1 -TimeZone -s DSC_3403.NEF
[Nikon] TimeZone: +01:00

If the feedback I'm leaving is not helpful then I can back off and let the experts do their thing. The original issue still stands though: Photoprism currently does not respect the offset of the clock in the camera that took the photo.

[lastzero]

It's helpful to learn about your needs. However, time ZONE and OFFSET have defined, distinct meanings (independent of how Exiftool labels the value +01:00, it is not a zone): https://www.w3.org/International/core/2005/09/timezone.html

Money Quote:

What is a "zone offset"? A zone offset is the difference in hours and minutes between a particular time zone and UTC. In ISO 8601, the particular zone offset can be indicated in a date or time value. The zone offset can be Z for UTC or it can be a value "+" or "-" from UTC. For example, the value 08:00-08:00 represents 8:00 AM in a time zone 8 hours behind UTC, which is the equivalent of 16:00Z (8:00 plus eight hours). The value 08:00+08:00 represents the opposite increment, or midnight (08:00 minus eight hours).

What is a "time zone"? A time zone is an identifier for a specific location or region which translates into a combination of rules for calculating the UTC offset. For example, when a website maintaining a group calendar in the United States schedules a recurring meeting for 08:00 Pacific Time, it is referring to what is sometimes known as wall time (so called because that is the time shown "on the clock (or calendar) on the wall"). This is not equivalent to either 08:00-08:00 or 08:00-07:00, because Pacific Time does not have a fixed offset from UTC; instead, the offset changes during the course of the year.

[lastzero]

If the timestamps have an offset, it is local time. This already is the default for Exif timestamps. If the metadata also contains the GPS coordinates, the ZONE and OFFSET should be correct and properly displayed. If the GPS coordinates are missing, then how does the Camera or other tools you use know the offset or zone?

[lastzero]

Also note that the latest release will sort by local times instead of UTC (if available). We may add settings for this in a future release.