GoogleLookupTimeZone for Quicktime tags

[grasdk]

Hello

With success I have tested the config-file made and posted by Stargeek here: https://exiftool.org/forum/index.php?topic=11026.msg58905

It works great on the jpg's that my Google Pixel 7 makes. However mp4s are another matter, since their GPS and DateTime tags by default are different from the ones in the jpg's.

I made a test-photo and a test-video at approximately the same time. PXL_20240723_195116532.jpg, and PXL_20240723_195119606.mp4

They were shot at in my local timezone of CEST (+02:00), but to test that timezone is ignored, I took the photos with my Phone's timezone set to Japan (+09:00), but GPS enabled (in Denmark).

Here are the results of running exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config -ee -G -gps* -createdate -datetimeoriginal -offset* -GoogleLookupTimeZone ./PXL_20240723_19511* (the curl download data output at 2nd to 4th line can be ignored):

Code Select Expand

======== ./PXL_20240723_195116532.jpg
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   159  100   159    0     0   1006      0 --:--:-- --:--:-- --:--:--  1012
[EXIF]          GPS Version ID                  : 2.2.0.0
[EXIF]          GPS Latitude Ref                : North
[EXIF]          GPS Latitude                    : 56 deg 5' 22.49"
[EXIF]          GPS Longitude Ref               : East
[EXIF]          GPS Longitude                   : 8 deg 7' 0.28"
[EXIF]          GPS Altitude Ref                : Above Sea Level
[EXIF]          GPS Altitude                    : 41.21 m
[EXIF]          GPS Time Stamp                  : 19:50:47
[EXIF]          GPS Img Direction Ref           : Magnetic North
[EXIF]          GPS Img Direction               : 277
[EXIF]          GPS Date Stamp                  : 2024:07:23
[Composite]     GPS Altitude                    : 41.2 m Above Sea Level
[Composite]     GPS Date/Time                   : 2024:07:23 19:50:47Z
[Composite]     GPS Latitude                    : 56 deg 5' 22.49" N
[Composite]     GPS Longitude                   : 8 deg 7' 0.28" E
[Composite]     GPS Position                    : 56 deg 5' 22.49" N, 8 deg 7' 0.28" E
[EXIF]          Create Date                     : 2024:07:24 04:51:16
[EXIF]          Date/Time Original              : 2024:07:24 04:51:16
[EXIF]          Offset Time                     : +09:00
[EXIF]          Offset Time Original            : +09:00
[EXIF]          Offset Time Digitized           : +09:00
[XMP]           Offset SDR                      : 0.000000
[XMP]           Offset HDR                      : 0.000000
[Composite]     Google Look Up Time Zone        : 02:00
======== ./PXL_20240723_195119606.mp4
[QuickTime]     GPS Coordinates                 : 56 deg 5' 21.12" N, 8 deg 6' 59.40" E
[Composite]     GPS Latitude                    : 56 deg 5' 21.12" N
[Composite]     GPS Longitude                   : 8 deg 6' 59.40" E
[Composite]     GPS Position                    : 56 deg 5' 21.12" N, 8 deg 6' 59.40" E
[QuickTime]     Create Date                     : 2024:07:23 19:51:23
    2 image files read
As far as I can gather from the config-file from Stargeek's post, it requires the EXIF-gps and EXIF:DateTimeOriginal tags. These are not present in the mp4, hence the GoogleLookupTimeZone* composite tags do not work.

One way to go about making it work for mp4's could be to copy the quicktime gps and createdate tags to equivalent exif tags, but I read somewhere on these forums that this is non-standard. I tried it anyway, but unsuccesfully (second command uses a simple assignment for testing purposes):

Code Select Expand

$ exiftool -ee '-exif:gpslatitude<composite:gpslatitude' -c "%d %d %.8f" test.mp4
    0 image files updated
    1 image files unchanged

$ exiftool "-exif:gpsposition='56 5 21.12000000 N, 8 6 59.40000000 E" -c "%d %d %.8f" test.mp4
Warning: Sorry, exif:GPSPosition doesn't exist or isn't writable
Nothing to do.

Another way to go about it would be patching the config-file to also work on the quicktime tags, I suppose. But the quicktime tag itself does not separate latitude and longitude, only the composite tags do that, and I'm unsure if these can be used?

If not, maybe I can use XMP gps-tags as intermediaries, but not sure if this is also out of spec for mp4 etc.

Would anyone be able to help on either of these solutions or suggest a different solution?

Any help is much appreciated!

----
PS: My ultimate goal is to store full timestamp including timezone in my mp4's Quicktime:CreationDate and/or Quicktime:DateTimeOriginal to have some tags that will give my videos the same readily available information as is present in the photos. I read in the first reply (also by StarGeek) in this topic: https://exiftool.org/forum/index.php?topic=13886.0, that these two tags would be suitable to hold such information.

[Phil Harvey]

If this is the TimeZoneLookup.config file you are using, then it doesn't specify EXIF tags at all, so it should work fine with MOV/MP4 videos since ExifTool will generate Composite GPSLatitude/GPSLongitude tags from the QuickTime:GPSCoordinates if it exists.  It is the GPSCoordinates that you should be using in these files:

exiftool "-gpscoordinates='56 5 21.12000000 N, 8 6 59.40000000 E" FILE

But you might want to get more specific about where GPSCoordinates is written.  It will go to UserData by default, but I think Mac programs may prefer it in the Keys metadata:

exiftool "-keys:gpscoordinates='56 5 21.12000000 N, 8 6 59.40000000 E" FILE

- Phil

[grasdk]

Hey

Thanks for the response. You brought me to a solution: Add datetimeoriginal, which is not present, but required. I focused too much on the GPS-stuff! 

If this is the TimeZoneLookup.config file you are using

Yes, this is it. 

But you might want to get more specific about where GPSCoordinates is written.  It will go to UserData by default, but I think Mac programs may prefer it in the Keys metadata:

The unaltered mp4 directly from the phone, has these data:

Code Select Expand

$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config -ee -G0:1:2 -gps* -createdate -datetimeoriginal -offset* -GoogleLookupTimeZone ./PXL_20240723_19511*.mp4
[QuickTime:UserData:Location] GPS Coordinates   : 56 deg 5' 21.12" N, 8 deg 6' 59.40" E
[Composite:Location] GPS Latitude               : 56 deg 5' 21.12" N
[Composite:Location] GPS Longitude              : 8 deg 6' 59.40" E
[Composite:Location] GPS Position               : 56 deg 5' 21.12" N, 8 deg 6' 59.40" E
[QuickTime:Time] Create Date                    : 2024:07:23 19:51:23
But once I copy createdate to datetimeoriginal, it works: exiftool '-quicktime:datetimeoriginal<{$createdate}Z' PXL_20240723_195119606.mp4

I'm adding UTC timezone, because Google started storing these timestamps in UTC in 2020 and I want the correct universal time to be listed. Here are the commands and the output.

Code Select Expand

$ exiftool '-quicktime:datetimeoriginal<{$createdate}Z' PXL_20240723_195119606.mp4
Warning: [minor] The ExtractEmbedded option may find more tags in the media data - PXL_20240723_195119606.mp4
    1 image files updated
$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config -ee -G0:1:2 -gps* -createdate -datetimeoriginal -offset* -GoogleLookupTimeZone ./PXL_20240723_19511*.mp4
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   159  100   159    0     0   1160      0 --:--:-- --:--:-- --:--:--  1169
[QuickTime:UserData:Location] GPS Coordinates   : 56 deg 5' 21.12" N, 8 deg 6' 59.40" E
[Composite:Location] GPS Latitude               : 56 deg 5' 21.12" N
[Composite:Location] GPS Longitude              : 8 deg 6' 59.40" E
[Composite:Location] GPS Position               : 56 deg 5' 21.12" N, 8 deg 6' 59.40" E
[QuickTime:Time] Create Date                    : 2024:07:23 19:51:23
[QuickTime:UserData:Time] Date/Time Original    : 2024:07:23 19:51:23Z
[Composite:Time] Google Look Up Time Zone       : 02:00

[grasdk]

Additional questions leading to my ultimate goal: To update quicktime:datetimeoriginal with local time andoffset.

The google-tag gives me the information I need to 1: Shift the time and 2: Add the timezone.

I tried a few things, and below there is a short "walkthrough". To cut it short, this is the command I would think should work, but doesn't:

$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-quicktime:datetimeoriginal<${createdate;ShiftTime("$self->GetValue('GoogleLookupTimeZone')")} $GoogleLookupTimeZone' test.mp4

Can this be fixed, or am I on a wild goose-chase? I can probably do it by some elaborate export of data to a separate file and then leverage awk and other tools, but it would be inefficient, so I'm hoping for an exiftool-only solution.

----

What I tried:

This works (simple values - shows that the raw offset-value is ok to use as parameter to ShiftTime):
exiftool '-quicktime:datetimeoriginal<${createdate;ShiftTime("+02:00")} +02:00' test.mp4
also this (where google timezone is used to change the timezone of the timestamp):
$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-quicktime:datetimeoriginal<${createdate;ShiftTime("+02:00")} $GoogleLookupTimeZone' test.mp4
but not this (it adds the offset, but does not shift the time):
$ $ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-quicktime:datetimeoriginal<${createdate;ShiftTime("$self->GetValue('GoogleLookupTimeZone')")} $GoogleLookupTimeZone' test.mp4

- I tried a few variations with and without double quiotes, but did not get the desired result.


----
If you wonder why I want to do this:

I want to automate setting local time and offset on mp4-files, even though google decided not to include this info by default to mp4-files only to jpg.
Step1: Set quicktime:DateTimeOriginal in UTC-format from CreateDate (check)
Step2: Update quicktime:DateTimeOriginal to Local Time and timezone using the now available GoogleLookupTimeZone tag. (missing)

- An optimization would probably be to update the config file for GoogleTimezone to accept CreateDate in addition to DateTimeOriginal. Then all could be done in 1 step.

[StarGeek]

Unfortunately, it's convention time for me and I'll be mostly AFK until Monday. Hopefully, you can find a workaround until then.  This is like the only day I'm not up at 6 am and back home at 9 pm or later.

I tried a few things, and below there is a short "walkthrough". To cut it short, this is the command I would think should work, but doesn't:

exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-quicktime:datetimeoriginal<${createdate;ShiftTime("$self->GetValue('GoogleLookupTimeZone')")} $GoogleLookupTimeZone' test.mp4

I'm pretty sure you don't want quotes around $self->GetValue. I can't test at the moment, but I think this is closer. At least, according to some other commands I have that use ShiftTime($self->GetValue

$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-quicktime:datetimeoriginal<${createdate;ShiftTime($self->GetValue("GoogleLookupTimeZone"))} $GoogleLookupTimeZone' test.mp4[/quote]

[grasdk]

Thanks Stargeek and Phil for your help here - and also in general

I arrived at this solution with your help and some input from ChatGPT as well.

The trick was to store the offset in a variable (using my $offset=) instead of inlining the lookup into ShiftTime.

$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-quicktime:da
tetimeoriginal<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)} $GoogleLookUpTimeZone' test.mp4

To augment my usecase, I updated the TimezoneLookUp.config (attached) to accept CreateDate as input also, so it now works if either DateTimeOriginal or CreateDate is present. I hope this can help others in the future.

[grasdk]

My final command stores adjusted timestamps in local time in:

• QuickTime:DateTimeOriginal
• QuickTime:CreationDate
• XMP:DateCreated

The command:
exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-QuickTime:DateTimeOriginal<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)}+$GoogleLookUpTimeZone' '-QuickTime:CreationDate<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)}+$GoogleLookUpTimeZone' '-XMP:DateCreated<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)}+$GoogleLookUpTimeZone' test.mp4

Caveat: It is assumed that CreateDate is UTC. For use with jpg, one should probably use an adjusted DateTime variable as base for the calculation, it could also be GPSDateTime, although it is a bit imprecise. Alternatively one could probably check for existing timezone offsets in the base timestamp... many options. But this is good for the current state of how Google phones save mp4-files.

Before, Command, After

Code Select Expand

$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config -ee -G0:1:2 -quicktime:createdate -quicktime:creationdate -quicktime:datetimeoriginal -xmp:createdate -DateCreated -UserComment test.mp4
[QuickTime:Time] Create Date                    : 2024:07:21 16:33:17
$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config '-QuickTime:DateTimeOriginal<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)}+$GoogleLookUpTimeZone' '-QuickTime:CreationDate<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)}+$GoogleLookUpTimeZone' '-XMP:DateCreated<${Createdate;my $offset=$self->GetValue("GoogleLookUpTimeZone");ShiftTime($offset)}+$GoogleLookUpTimeZone' test.mp4
Warning: [minor] The ExtractEmbedded option may find more tags in the media data - test.mp4
    1 image files updated
$ exiftool -config /usr/share/applications/ExifTool/config_files/TimezoneLookUp.config -ee -G0:1:2 -quicktime:createdate -quicktime:creationdate -quicktime:datetimeoriginal -xmp:createdate -DateCreated -UserComment test.mp4
[QuickTime:Time] Create Date                    : 2024:07:21 16:33:17
[QuickTime:Keys:Time] Creation Date             : 2024:07:21 18:33:17+02:00
[QuickTime:UserData:Time] Date/Time Original    : 2024:07:21 18:33:17+02:00
[XMP:XMP-photoshop:Time] Date Created           : 2024:07:21 18:33:17+02:00
Note: The updated TimezoneLookUp.config attached earlier, was used for this.