Identifying discrepancies and re-writing file modify date/time in .MOV files

[grole]

Hi there,

I've just discovered that many of my movie files from my 70D have lost their original date/time. I presume it was stored in either [File] or [EXIF], looking at the readout of the file:

C:\Users\Administrator>exiftool -a -G0 -time:all e:\temp\shift
======== e:/temp/shift/2015-03-21_18-49-36-27_70D.MOV
[File]          File Modification Date/Time     : 2015:04:20 18:49:36+02:00
[File]          File Access Date/Time           : 2018:04:10 20:37:55+02:00
[File]          File Creation Date/Time         : 2018:04:10 20:37:55+02:00
[EXIF]          Modify Date                     : 2015:04:20 18:49:36
[EXIF]          Date/Time Original              : 2015:04:20 18:49:36
[EXIF]          Create Date                     : 2015:04:20 18:49:36
[MakerNotes]    Time Zone                       : +01:00
[MakerNotes]    Time Zone City                  : Paris
[MakerNotes]    Daylight Savings                : Off
[EXIF]          Sub Sec Time                    : 27
[EXIF]          Sub Sec Time Original           : 27
[EXIF]          Sub Sec Time Digitized          : 27
[QuickTime]     Create Date                     : 2015:04:20 18:49:36
[QuickTime]     Modify Date                     : 2015:04:20 18:49:36
[QuickTime]     Track Create Date               : 2015:04:20 18:49:36
[QuickTime]     Track Modify Date               : 2015:04:20 18:49:36
[QuickTime]     Media Create Date               : 2015:04:20 18:49:36
[QuickTime]     Media Modify Date               : 2015:04:20 18:49:36
[QuickTime]     Track Create Date               : 2015:04:20 18:49:36
[QuickTime]     Track Modify Date               : 2015:04:20 18:49:36
[QuickTime]     Media Create Date               : 2015:04:20 18:49:36
[QuickTime]     Media Modify Date               : 2015:04:20 18:49:36
[QuickTime]     Track Create Date               : 2015:04:20 18:49:36
[QuickTime]     Track Modify Date               : 2015:04:20 18:49:36
[QuickTime]     Media Create Date               : 2015:04:20 18:49:36
[QuickTime]     Media Modify Date               : 2015:04:20 18:49:36
[Composite]     Create Date                     : 2015:04:20 18:49:36.27
[Composite]     Date/Time Original              : 2015:04:20 18:49:36.27
[Composite]     Modify Date                     : 2015:04:20 18:49:36.27
    1 directories scanned
    1 image files read

I imported these using Downloader Pro, which picked up an original Date of 21 March 2015, for this example, according to the file name.

In order to correct this, I'd need to first know which ones are wrong, since not all have this problem. Would there be a method to loop through a directory and compare the date as extracted from the file name to the current file modify date, and mark those which are not the same somehow? It'd be enough to compare the date as the time is strangely not changed: 18:49:36.

What's happened is that the file modify date seems to be the date on which I processed the files: in this case Apr 20, after downloading on on Apr 10, whereas the film was taken on Mar 21.

After that, I'd need a way to batch overwrite file modify date using the file name date for those marked ones. As far as I know Exiftool won't do it? I've been using AttributeMagic but it's pretty basic.

Thanks in advance!

[Phil Harvey]

Why not just go ahead and overwrite the filesystem modification date for all files?:

exiftool "-filemodifydate<filename" DIR

- Phil

[grole]

Hi Phil,

Thanks for the quick answer. You're right, I could do that. I guess that means exiftool will do it - great! I suppose "filename" would have to be a string of some sort to extract the correct form from this template: "YYYY-MM-DD_HH-MM-SS-SS_MODEL"?

However I would like to be able to identify these problem files because I'll need to redo the resizing which I do before uploading. Since it's time and processor intensive, I don't want to do unnecessary ones.

The other reason would be to analyse them and find the problem perhaps. Also, I've had another issue where the time is often off by 1 hour. That's probably due to my time zone of UTC+01:00 (UTC+02:00 in summer) and certain software muddling with that.

Cheers

[StarGeek]

Thanks for the quick answer. You're right, I could do that. I guess that means exiftool will do it - great! I suppose "filename" would have to be a string of some sort to extract the correct form from this template: "YYYY-MM-DD_HH-MM-SS-SS_MODEL"?

While I can't help with your other questions, this can be answered by reading the 3rd paragraph of FAQ 5.

[Phil Harvey]

Testing to see if the time is the same would require some fancy reformatting of the file name and FileModifyDate so that could be compared.  I don't have time to do this now, but I'll have a try at it tomorrow.

- Phil

[grole]

@StarGeek: Thanks! I knew that would be the simplest part of it. I'll check the FAQ.

@Phil: No rush of course! Any help is appreciated. I can see how the films I truncated (using Video to Video) have lost their date, what perplexes me is how the other ones, although not all, have been changed. For these all I did was download them, then move to a new folder.

[Phil Harvey]

OK.  Here is a command to compare the date/time from a FileName with the FileModifyDate, and print the names of the files where there is a difference:

Code Select Expand

exiftool -filename -if "${filename;tr/0-9//dc;$_=substr($_,0,14)} ne ${filemodifydate;tr/0-9//dc;$_=substr($_,0,14)}" DIR

Here I am comparing the first 14 digits contained in the values of these tags to see if they are the same.

- Phil

[grole]

Thanks Phil, that's great! I tried it on a couple of test batches. I was first confused because I think the results are reversed. It indeed lists the files which failed the condition but at the bottom it says that those files were "read", and the other ones failed. Example below: 14 files, 8 of which fail. Anyway that's not an issue.

C:\Users\Administrator>exiftool -filename -if "${filename;tr/0-9//dc;$_=substr($
_,0,14)} ne ${filemodifydate;tr/0-9//dc;$_=substr($_,0,14)}" e:\temp\shift
======== e:/temp/shift/2014-10-03_17-50-41-73_70D_CLIP_1.mp4
File Name                       : 2014-10-03_17-50-41-73_70D_CLIP_1.mp4
======== e:/temp/shift/2014-10-05_08-10-14-94_70D_CLIP_1.mp4
File Name                       : 2014-10-05_08-10-14-94_70D_CLIP_1.mp4
======== e:/temp/shift/2014-10-12_15-44-28-01_70D.mp4
File Name                       : 2014-10-12_15-44-28-01_70D.mp4
======== e:/temp/shift/2014-10-12_15-45-31-08_70D.mp4
File Name                       : 2014-10-12_15-45-31-08_70D.mp4
======== e:/temp/shift/2015-03-21_18-43-36-00_70D.mp4
File Name                       : 2015-03-21_18-43-36-00_70D.mp4
======== e:/temp/shift/2015-03-21_18-48-26-00_70D.mp4
File Name                       : 2015-03-21_18-48-26-00_70D.mp4
======== e:/temp/shift/2015-04-19_17-28-08_SX240.mp4
File Name                       : 2015-04-19_17-28-08_SX240.mp4
======== e:/temp/shift/2015-04-21_16-11-31_SX240.mp4
File Name                       : 2015-04-21_16-11-31_SX240.mp4
    1 directories scanned
    6 files failed condition
    8 image files read

What came out is a lot more failed conditions than I was finding myself. The obvious ones, where the date is wrong, I was finding. Then there are ones where the hour is off by 1, like I mentioned. Those are hard to see. And then there is a new set which baffled me at first. See the attached screenshot. The file, for example, 2015-01-27_07-55-58-42_70D.MOV should be correct and it appears to be in ACDSee. I also checked ExiftoolGUI: it also shows 7:55 (+01:00). Only exiftool itself shows the actual time of 6:55, albeit all other times are 7:55. And indeed, when I upload to Google Photos, 6:55 is the time it uses.

So basically I'll have to re-write those ones as well. I'll now try to figure out a -filemodifydate<filename command to write it using the FAQ.

Could I perhaps integrate that into the main command? So, if the condition fails, then overwrite the modify date based on the file name?

Thanks again

C:\Users\Administrator>exiftool -a -G0 -time:all e:\temp\shift
======== e:/temp/shift/2015-01-27_07-55-58-42_70D.MOV
[File]          File Modification Date/Time     : 2015:01:27 06:55:58+01:00
[File]          File Access Date/Time           : 2018:04:11 21:41:50+02:00
[File]          File Creation Date/Time         : 2018:04:11 21:41:50+02:00
[EXIF]          Modify Date                     : 2015:01:27 07:55:58
[EXIF]          Date/Time Original              : 2015:01:27 07:55:58
[EXIF]          Create Date                     : 2015:01:27 07:55:58
[MakerNotes]    Time Zone                       : +01:00
[MakerNotes]    Time Zone City                  : Paris
[MakerNotes]    Daylight Savings                : Off
[EXIF]          Sub Sec Time                    : 42
[EXIF]          Sub Sec Time Original           : 42
[EXIF]          Sub Sec Time Digitized          : 42
[QuickTime]     Create Date                     : 2015:01:27 07:55:58
[QuickTime]     Modify Date                     : 2015:01:27 07:55:58
[QuickTime]     Track Create Date               : 2015:01:27 07:55:58
[QuickTime]     Track Modify Date               : 2015:01:27 07:55:58
[QuickTime]     Media Create Date               : 2015:01:27 07:55:58
[QuickTime]     Media Modify Date               : 2015:01:27 07:55:58
[QuickTime]     Track Create Date               : 2015:01:27 07:55:58
[QuickTime]     Track Modify Date               : 2015:01:27 07:55:58
[QuickTime]     Media Create Date               : 2015:01:27 07:55:58
[QuickTime]     Media Modify Date               : 2015:01:27 07:55:58
[QuickTime]     Track Create Date               : 2015:01:27 07:55:58
[QuickTime]     Track Modify Date               : 2015:01:27 07:55:58
[QuickTime]     Media Create Date               : 2015:01:27 07:55:58
[QuickTime]     Media Modify Date               : 2015:01:27 07:55:58
[Composite]     Create Date                     : 2015:01:27 07:55:58.42
[Composite]     Date/Time Original              : 2015:01:27 07:55:58.42
[Composite]     Modify Date                     : 2015:01:27 07:55:58.42
    1 directories scanned
    1 image files read

[Phil Harvey]

My command attempted to extract the FileName of any file with a different date.  Ones that fail the condition should have the same date.  (I used the "ne" operator, which means "not equal to")

Instead of -filename in this command, you could do "-filemodifydate<filename" if you want.

- Phil

[grole]

Thanks for clarifying that. Seems obvious now.

This will work well. I can first run the report to see which movies will be modified and mark them. Then run the re-write, and re-upload only those files.

Once again you saved my project, thanks Phil!

[grole]

Hi again.

I've been using the above method to correct all of my video files back to 2012 or so and it's working fine. I was under the impression that newer files were more or less in order, but the check has revealed that a good many of my files even from this year have the discrepancy. About 30% in fact. So I wanted to understand where this comes from.

Aside from some 9 hour differences from files taken during a trip to western Canada - probably my fault - the difference is always exactly 1 hour. I also noticed that most of the discrepancies occur in March and October.

In March, for the dates leading up to the change to DST on (March 26 in 2017) , the file modify date is 1h ahead of the date/time original (=filename). In October, in the days leading up to the change to normal time (Oct 29 in 2017), the date is 1h behind. After each time change, the discrepancies stop.

I don't have a problem with .jpg files, as they retain their date/time original. But out of interest I ran the test for 2017. Same results for about 15% of files. Discrepancies started on March 19th, and abruptly stopped on March 25th, just before the change to DST on the 29th. They start again on Sept. 28th, and stop abruptly on Oct. 27, again just before the time change.

It seems obvious to me that the problem is linked with DST, rather than with my time zone of UTC +1 as I thought before. The question is, what would cause this and why for only some files?

I did some extensive testing today using new files on different cameras, checking the values before and after each step of my normal workflow. I didn't find any problems. Then again, the time change has already happened. I'm in the "safe zone". For info, my workflow (for video) is as follows:

- download from SD card in card reader using Downloader Pro to a folder and file system based on date/time/model, along with the raw and jpg files
- extract and move all video files to a temporary collection folder
- copy, using Downloader Pro, all video files from the collection folder to their final filed folders (different from the initial ones but again date based)
- make a resized copy in .mp4 format using Video to Video
- upload the resized files to Google Photos - this is where the discrepancy causes a problem, GP has only the file modify date to deal with
Note: the resizing is done to drastically reduce upload times, seeing as Google downsizes video anyway, and these are just for sharing purposes

And yes I do change my camera times to DST on time. I just change the time, not the option of switching DST on or off. I tested both of these methods today and found no difference. My cameras are also set to UTC +1 (Paris). I also tried setting them to UTC, but again found no difference.

I've attached the results of 2017 & 2018.

I'll be looking into this further but thought I'd post these results and questions in case Phil, or anyone, has any enlightening knowledge of the subject.

Thanks for listening...

[Phil Harvey]

I think you may be putting way too much effort into this because Windows has known bugs involving daylight savings time.

However, with the known bugs one might expect the time to be off for half of the year.  If it is just wrong near the shifts, it could maybe be due to a change in the daylight savings time boundaries as happened in North America in 2007.

- Phil

[grole]

Hi Phil,

You're right, I'm definitely putting too much effort into it. But I wanted to know whether it was something I was doing wrong. Until now I suspected a bug or poor behavior of one software or the other. I wouldn't have expected it to be Windows itself (although I should have perhaps).

Whether it has to do with the boundaries, or a bug, or both, the important thing is that it's a system problem as opposed to some software or the cameras. That's annoying, but I'm glad to have it clarified.

Probably the best bet is to just "overwrite" the file modify date using the file name, as above, at least before re-sizing for upload, if not in general. If I've understood correctly, the file modify date isn't actually in the file, but just "assigned" to it by Windows? So conceivably I'd have to re-do the re-write if I changed systems (or time zones), but there wouldn't be any effect on the the metadata, so no harm in file damage by repeating it . In that case I could also run it for the jpgs, just in case it makes a difference for one software or the other.

As a matter of fact, a similar issue drove me to use date/time based file naming in the first place. ACDSee was displaying some dates shifted by 1 hour, but not for others, which made a chronological sort between cameras impossible. Ironically, due to Google Photos and video files, I'm forced to inspect the time again and try to correct it. The reason I'm still sticking with GP, by the way, is that it's the only service with real unlimited storage for "free".

Thanks again