EXIF Tool and video files which are saved with UTC timestamps

Question

I'm curious if anybody could give me some insight on a current issue I'm having.

For some context, my wife and I have an Android phone (Moto G5 Plus). The phones sync all items in DCIM folder to my Ubuntu Server over SFTP. The items are stored in an "unsorted_pictures" directory. Once a night, a cron job runs, executing a script that automatically copies the contents from unsorted_pictures over to pictures/year/month. Later on (after the phones max in space) I'll purge the unsorted_pictures directory as a maintenance step.

The script, for whatever it's worth, is as follows:

#!/bin/bash
exiftool -overwrite_original_in_place -P -if 'not $CreateDate' '-CreateDate<FileModifyDate' -r /mnt/vault/unsorted_pictures/staging
exiftool -o . '-Directory<CreateDate' -d /mnt/vault/pictures/%Y/%m -r /mnt/vault/unsorted_pictures/staging
exit

So basically, the first exiftool line checks if CreateDate exists. If it doesn't, it uses the FileModifyDate to create the CreateDate parameter. This is really only useful for pictures saved via Hangouts conversations as Google seems to strip exif data. (?!) After that, the second exiftool line does the copying+sorting.

This process is kind of amazing and I'm very happy with it. There's one snafu though -- the video files seem to be recording in UTC time, whereas the pictures seem to be recorded in local time. Below is a video file that was taken, immediately transferred to my server, and exiftool was ran against it to check all of the time stamps.

administrator@vault:/mnt/vault/unsorted_pictures$ exiftool -time:all -s VID_20171225_214456599.mp4
FileModifyDate      : 2017:12:25 21:47:02-05:00
FileAccessDate      : 2017:12:25 21:47:00-05:00
FileInodeChangeDate : 2017:12:25 21:47:02-05:00
CreateDate          : 2017:12:26 02:45:00
ModifyDate          : 2017:12:26 02:45:00
TrackCreateDate     : 2017:12:26 02:45:00
TrackModifyDate     : 2017:12:26 02:45:00
MediaCreateDate     : 2017:12:26 02:45:00
MediaModifyDate     : 2017:12:26 02:45:00

administrator@vault:/mnt/vault/unsorted_pictures$ date
Mon Dec 25 21:47:23 EST 2017

As you can see, the CreateDate is set, seemingly, to a future time. Further below I ran the 'date' command so you can see the current local time when this took place. In the upper lines you can see the time stamps ending with 05:00, which is my understanding that it's the time differential to UTC.

On to my actual questions:

1) I tried multiple camera apps on my Motorola, but all of them yielded the exact same behavior. This suggests that it's not an app-specific setting. Is there a way to keep my videos from being recorded in UTC format? Pictures are fine, it's just videos. I find this to be a little strange, but online searching suggests it's common, but I have yet to hear and understand why.

2) I'm sure I can add some logic within my script to simply sort pictures via CreateDate and sort video file types via FileModifyDate, or even added some extra parameter to systematically re-date all videos back by 5 hours. But I got to wondering, is there a means to have exiftool look at an mp4 file and acknowledge the UTC timing difference? I know video files are a different beast and exiftool is largely focused on pictures, but even still, given exiftool does such a good job with acknowledging metadata even on these videos, it had me thinking there might be a way to tweak it to view the non-UTC time. Or perhaps there's no hope if the phone is recording in UTC time. /shrug

Perhaps this is even more reason to sort by year/month instead of year/month/day, as I won't even tell that videos are shifted by 5 hours since they'll all be within that month's directory. But still, I find myself intrigued by this and wondering... why?

Any help or insight would be very much appreciated!

Answer 1

According to Phil Harvey (exiftool's creator), Quicktime timestamps are supposed to be set to UTC time as per the standard. But it seems a lot of cameras don't do this, so exiftool doesn't assume a timezone and accepts the time as written. See fourth paragraph under Quicktime tags

Exiftool includes an option to correct for this. If you are running exiftool ver 9.40 or later, you can add -api QuickTimeUTC to the command and it will assume that the timestamps are correctly written as UTC and convert them to local time.

Answer 2

I suggest you create a new "CreateDateLocal" tag in which you store the creation time as local time.

You do that by the config.xml. In 'Image::ExifTool::XMP::xmp', you add:

CreateDateLocal => { Groups => { 2 => 'Time' }, Shift => 'Time' },  # Stores the create date in local time (for videos)

It will probably look like this after the insert:

    # XMP tags may be added to existing namespaces:
    'Image::ExifTool::XMP::xmp' => {
        # Example 5.  XMP-xmp:NewXMPxmpTag
        NewXMPxmpTag => { Groups => { 2 => 'Author' } },

        # add more user-defined XMP-xmp tags here...
        CreateDateLocal => { Groups => { 2 => 'Time' } },  # Stores the create date in local time (for videos)
    },

For videos with UTC, run this:
exiftool -r -ext mp4 -api QuickTimeUTC "-CreateDateLocal<CreateDate" .

For videos in local time, run this:
exiftool -r -ext mp4 "-CreateDateLocal<CreateDate" .

You can also run it on images and then simply use "CreateDateLocal" instead of "CreateDate" for organizing. End of simple solution

A bit more advanced solution:

You can create a new composite tag, which falls back to CreateDate, when CreateDateLocal is missing. With this in place, you will only need to tag the videos with UTC timestamp (that is: it will save you from tagging all the images and it will also save you from tagging the videoes that with local timestamp).

Here is how:

Search for "'Image::ExifTool::Composite'" in the config. Inside {}, insert the following:

# TheDateTaken is best attempt to get the date the photo was taken - preferrably in local time
# First the custom tag "CreateDateLocal" is checked. This is expected to be set manually on all videos.
# - For videos with UTC, run this: `exiftool -ext mp4 -api QuickTimeUTC "-CreateDateLocal<CreateDate" .`
# - For videos in local time, run this: `exiftool -ext mp4 "-CreateDateLocal<CreateDate" .`
# If CreateDateLocal isn't set, we asume it is an image. Images are always set in local time.
# There are multiple tags used for storing creation time. We check them all (listed in Desire)

CreateDateLocalWithFallback => {
    Desire => {
        0 => 'CreateDateLocal',
        1 => 'DateTimeOriginal',
        2 => 'CreationDate',
        3 => 'CreateDate',
    },

    Groups => { 2 => 'Time' },
    ValueConv => sub {
        $val = shift;
        # Loop through dates
        foreach $date (@$val) {
            next if not defined $date;
            return $date if ($date =~ /^[12]\d\d\d/);    # if it doesn't start with four digits, skip, as it is not a valid date            
        }               
        return undef;               
    },            
    Shift => 'Time',
    Validate => 'ValidateExifDate($val)',
    PrintConv => '$self->ConvertDateTime($val)',
},

Extend solution by also parsing date from filename

Samsung stores the creation date in the filename. It can be used as fallback. This composite tag can extract the date (to use it in CreateDateLocalWithFallback, simply add "DateFromFilename" to the "Desire" array)

DateFromFilename => {
    Desire => {
        0 => 'OriginalFileName',
        1 => 'FileName',
    },

    Groups => { 2 => 'Time' },
    ValueConv => sub {
        $val = shift;

        foreach $filename (@$val) {
            # See if filename resembles date (it does on Samsung)
            # Samsung naming: "20150827_165859.jpg"
            if ($filename =~ /^\d{8}_\d{6}/) {
                $year = substr($filename, 0, 4);
                $month = substr($filename, 4, 2);
                $day = substr($filename, 6, 2);
                $hour = substr($filename, 9, 2);
                $minute = substr($filename, 11, 2);
                $second = substr($filename, 13, 2);
                $y = int($year);
                $mo = int($month);
                $d = int($day);
                $h = int($hour);
                $mi = int($minute);
                $s = int($second);
            
                #return '2015:08:27 16:58:59';
                #return @$val[0];
                if (
                    (($y > 1900) && ($y < 2400)) &&
                    (($mo >= 1) && ($mo <= 12)) &&
                    (($d >= 1) && ($d <= 31)) &&
                    (($h >= 0) && ($h <= 24)) &&
                    (($mi >= 0) && ($mi <= 60)) &&
                    (($s >= 0) && ($s <= 60))
                ) {
                    # '2015:08:27 16:58:59';
                    return $year . ':' . $month . ':' . $day . ' ' . $hour . ':' . $minute . ':' . $second; 
                }
            }               
        }
        return undef;               
    },            
    Shift => 'Time',
    Validate => 'ValidateExifDate($val)',
    PrintConv => '$self->ConvertDateTime($val)',
    #PrintConvInv => '$self->InverseDateTime($val,0)',
},