how to deduct the time zone from Filename (local time) and CreateDate (in UTC)?

[Jean-Pierre]

I find more and more cameras and smartphones which store a video (or some even photos) with a filename based on local time like
  Video_20240226_162000.mp4
and providing all the internal dates stored in UTC (especially CreateDate), like
  CreateDate : 2024-02-26T14:20:00Z 
(in ISO 8601 format).
For a human reader it is quite obvious, that this was taken in time zone +02:00  as the difference between both time representations.

I have tried to find a way of automatically construct the complete (local time including time zone)
  XMP:DateTimeOriginal  and/or  Quicktime:DateTimeOriginal 
(or alternatively the TimeZone-Offset) from this in ExifTool - but I have failed up to now.
Does anybody have a solution to this ?


The QuickTimeUTC just assumes the same time zone as the system setting in my windows computer, which is often a wrong guess:

But in the case of the video time stamps that are set to UTC, you can add the -api QuickTimeUTC option.  This will tell exiftool to adjust the integer based time stamps, the ones that cannot include a time zone, to/from UTC based upon the current time zone of the computer.

[StarGeek]

I did something similar in this post but since the filename isn't in an actual date/time format, it has to be reformatted.

The formatting you want to use to return the timezone would be
${Filename;s/.*(\d{4})(\d\d)(\d\d)_(\d\d)(\d\d)(\d\d).*/$1:$2:$3 $4:$5:$6/;$_.=TimeZoneString((GetUnixTime($_)-GetUnixTime($self->GetValue('CreateDate')))/60)}

This first takes the filename, extracts the date/time numbers, and reformats them. Using your example, FileName now has a value of 2024:02:26 16:20:00.

The, the complicated part.  It takes this value and uses the Perl Concatenation Assignment Operator .= to append data to the end of that value.

First, the date/time of the filename is converted to a Unix time stamp using exiftool's GetUnixTime() function. Then, $self->GetValue is used to get the value of the CreateDate tag, which should already be in UTC, and convert that to Unix time.  The UTC Unix time is then subtracted from the Local Unix time.  This is divided by 60 to get the number of minutes.  That value is then passed to exiftool's TimeZoneString function (which requires minutes as input).  The result is the time zone value and this is the value that is appended to the filename with the .= operator.

Example using a jpeg with your filename and CreateDate value

Code Select Expand

C:\>exiftool -G1 -a -s -AllDates Y:\!temp\x\y\z\Video_20240226_162000.jpg
[ExifIFD]       CreateDate                      : 2024:02:26 14:20:00

C:\>exiftool  "-xmp:DateTimeOriginal<${Filename;s/.*(\d{4})(\d\d)(\d\d)_(\d\d)(\d\d)(\d\d).*/$1:$2:$3 $4:$5:$6/;$_.=TimeZoneString((GetUnixTime($_)-GetUnixTime($self->GetValue('CreateDate')))/60)}"  Y:\!temp\x\y\z\Video_20240226_162000.jpg
    1 image files updated

C:\>exiftool -G1 -a -s -AllDates Y:\!temp\x\y\z\Video_20240226_162000.jpg
[XMP-exif]      DateTimeOriginal                : 2024:02:26 16:20:00+02:00
[ExifIFD]       CreateDate                      : 2024:02:26 14:20:00

[Martin B.]

I have found the following tags are always in localtime for me (i.e. the date/time it was when and where the video was taken):

• all iPhones and iPads, MOV: CreationDate
• Canon 70D, MOV: DateTimeOriginal
• Canon ELPH 340 HS, MP4: DateTimeOriginal
• Canon R6, MP4: DateTimeOriginal
• Canon G5X Mark II, MP4: DateTimeOriginal

I have also found the localtime in the DateTimeOriginal value of .THM sidecar files of older cameras (Canon S90 and Sony DSC-W80).

I'd like to know if this is true of these cameras for other people, and if there are similar heuristics for other types of cameras.

[blue-j]

I thought I would add a side comment that you cannot reliably discern time zone from UTC offset - it's not a "reversible" transformation.  More than one time zone can share an offset, and daylight savings time will or will not be being observed as well.  Without actual time zone data, we are left to our best inference.

- J

[wywh]

The UTC Unix time is then subtracted from the Local Unix time

This seems to work with .mp4. QuickTimeUTC=0 is disabled in the command because otherwise Keys:CreationDate is set to +00:00 (the same applies if XMP-exif:DateTimeOriginal or UserData:DateTimeOriginal is used instead):

Code Select Expand

exiftool -a -G1 -s -api QuickTimeUTC=1 -Time:All Video_20240226_162000.mp4
[QuickTime]     CreateDate                      : 2024:02:26 16:20:00+02:00

exiftool  -m -P -overwrite_original -api LargeFileSupport=1 -api QuickTimeUTC=0 '-Keys:CreationDate<${Filename;s/.*(\d{4})(\d\d)(\d\d)_(\d\d)(\d\d)(\d\d).*/$1:$2:$3 $4:$5:$6/;$_.=TimeZoneString((GetUnixTime($_)-GetUnixTime($self->GetValue("CreateDate")))/60)}' Video_20240226_162000.mp4

exiftool -a -G1 -s -api QuickTimeUTC=1 -Time:All Video_20240226_162000.mp4
[QuickTime]     CreateDate                      : 2024:02:26 16:20:00+02:00
[Keys]          CreationDate                    : 2024:02:26 16:20:00+02:00
edit: It works also when timezone is set to New York in exiftool (i.e. without touching the computer's clock):

Code Select Expand

exiftool -a -G1 -s -api QuickTimeUTC=1 -api TimeZone=America/New_York -Time:All Video_20240226_092000.mp4
[QuickTime]     CreateDate                      : 2024:02:26 09:20:00-05:00

exiftool  -m -P -overwrite_original -api LargeFileSupport=1 -api QuickTimeUTC=0 -api TimeZone=America/New_York '-Keys:CreationDate<${Filename;s/.*(\d{4})(\d\d)(\d\d)_(\d\d)(\d\d)(\d\d).*/$1:$2:$3 $4:$5:$6/;$_.=TimeZoneString((GetUnixTime($_)-GetUnixTime($self->GetValue("CreateDate")))/60)}' Video_20240226_092000.mp4

exiftool -a -G1 -s -api QuickTimeUTC=1 -api TimeZone=America/New_York -Time:All Video_20240226_092000.mp4
[QuickTime]     CreateDate                      : 2024:02:26 09:20:00-05:00
[Keys]          CreationDate                    : 2024:02:26 09:20:00-05:00

...without '-api TimeZone=America/New_York' that is:

exiftool -a -G1 -s -api QuickTimeUTC=1 -Time:All Video_20240226_092000.mp4
[QuickTime]     CreateDate                      : 2024:02:26 16:20:00+02:00
[Keys]          CreationDate                    : 2024:02:26 09:20:00-05:00
- Matti

[StarGeek]

I didn't think of checking the output with -api QuickTimeUTC option.

edit: It works also when timezone is set to New York in exiftool (i.e. without touching the computer's clock):

Geek is still sad that the -api TimeZone option doesn't work in Windows. (Why is Geek suddenly talking about himself in third person?)

[Phil Harvey]

@Geek:   I think it should be possible to get this working if I included the POSIX libraries in the distribution, but I'm pretty sure that it would bloat the distribution package more than it would be worth for this one feature.

- Phil

[StarGeek]

Ahh, I'll have to look into that.

A quick check by testing with use POSIX; shows that it is available for my setup.  Now I just need to dig into the specific functions.

Edit:

Code Select Expand

C:\>exiftool -p "${Filename;use POSIX;$_= strftime('%Z', localtime())}" y:\!temp\Test4.jpg
Pacific Standard Time
This is probably something I can work with. 

[Phil Harvey]

Hmmm.  If it is available when you run ExifTool, then ExifTool should use it. 

There must be some other reason the API TimeZone option doesn't work.  Likely the tzset function isn't supported for Windows.

- Phil

[Jean-Pierre]

I am very thankful to StarGeek that he could provide (so promptly!) with his exiftool-line-of-code the "wand" to extract robustly the complete DateTimeOriginal information from filename and CreateDate (as long as the latter is stored as UTC by the camera).

I did something similar in this post but since the filename isn't in an actual date/time format, it has to be reformatted.

I have now tested it for many photo and video files from different cameras (see red background in the attached table), and it worked perfectly for photos, and with only little deficits for (long) videos.

First, it is very robust to different surrounding texts patterns of the Date_Time string in the filename, performing more predictable than "-DateTimeOriginal<$filename" (including also Ignoring characters at start of filename ...).

Second, there is a time shift (of 0..2 s processing time for photos, and more significantly caused by the recording time of the video) between creating Filename and CreateDate in the camera. This can lead (especially for videos longer than about 30s) to time zones ending in something like xx:59 instead of xy:00, with larger deviations for videos with durations of several minutes.

In order to keep it best working for photos and videos I propose a little extension to the TimeZoneFormat conversion, restricting the result to the nearest time zone with multiples of 30 minutes, and thus tolerating a time shift for video durations of up to 15min (0.5*30min) and no matter which of Filename and CreateDate was created first:

Code Select Expand

-MWG:DateTimeOriginal<${Filename;s/.*(\d{4})(\d\d)(\d\d)_(\d\d)(\d\d)(\d\d).*/$1:$2:$3 $4:$5:$6/;$_.=TimeZoneString(int((GetUnixTime($_)-GetUnixTime($self->GetValue('CreateDate')))/1800+0.5)*30)} especially the part

Code Select Expand

int( ... )/1800+0.5)*30)
The attached table is also meant as an answer to the discussion topic of Martin B.

I have found the following tags are always in localtime for me ...

Observations: Over the years (the cameras in the list are sorted by age), the local time has been established to be indicated in the filename of a photo or video (beginning of recording); but I do not know a standard for this fact. It seems to be just best practice for human handling, although many different maker-specific formats exist.

The UTC as a global reference frame is no longer given through GPSDateTime, but rather in CreateDate (which has been given in local time for many years, i.e. in older cameras). The Quicktime standard for videos with the requirement of UTC for integer time stamps is probably the reason for this shift in practice. Global time ordering in a globally communicating society needs to rely on a measure of common time instants, which UTC provides. It is strange, that Samsung S20FE still stores CreateDate for photos in Local time, but for videos in UTC. Best would be the same process of data extraction for photos and videos !

Nevertheless, missing is (to my knowledge) a clear standard to indicate local time and time zone of a taken picture or video. The main purpose for this is the relevance for a single person, who consciously lives and sorts events in his/her local time. My understanding is, that this should be served by the DateTimeOriginal metadata (and its MWG variants) with the given option of indicating a time zone in it. Especially for videos but also for some newer cameras this info is not yet easily available (see the red entries in the table) - and StarGeeks "wand" has helped me a lot for solving this automatically at least for my personal needs!

Jean-Pierre

[StarGeek]

Likely the tzset function isn't supported for Windows.

Thinking back, I think I went down this rabbit hole before and Windows just doesn't properly support tzset, or more accurately, supports its own, very stupid, version of it.  I found multiple web pages where it is noted that Strawberry Perl had this feature removed, and also pages that point out that Python on Windows doesn't have it.

Most pages point to using DateTime::TimeZone, but unfortunately, on Windows, it uses a .dll and I've never found a way to get the Windows executable to load a .dll.  I've even copy/pasted all DateTime::TimeZone code and .dll into the %temp% directory exiftool runs from, in every /lib type directory I found there.  Nothing worked.  The only solution was to use the actual Perl version.

[Martin B.]

The attached table is also meant as an answer to the discussion topic of Martin B.

Jean-Pierre, please check your messages (click "My Messages" at the very top of this page).
Thanks,

Martin