Mac LAK

Q: Video metadata date is incorrect: set to sharing date and not creation date

iOS 10, iPhone 6+, Windows 10. Photos and videos are shared through iCloud, but NOT stored on iCloud.

 

 

Once synced on my PC, photos and videos have a mangled name (for example, "016a1f2e11dfcc680e1009cb96346b67678fb2c6bc.jpg"). Obviously, this is not acceptable at all for me when I organize them on my NAS: I prefer a meaningfull, sortable name, like "2016-09-16 18-15-20259 (Apple iPhone 6 Plus) .jpg".

To achieve this, I simply read EXIF informations directly from the picture, and I rename the picture accordingly. No problem for photos.

 

But for videos... I do the same, globally, but with fewer informations. I can extract only creation date/time from videos' metadata, not the device which captured the video. Sad, but I didn't find anything else, and at least it's automatic.

 

Recently, I figured out that the date inside the video file is NOT correct: it's the timecode of SHARING through iCloud, not the timecode of CAPTURE. So I have some videos with a two weeks delta between the real creation and the "official" date!

I checked this on my phone: date is correct (once video is not anymore in full screen, the date appears in the top of the screen, and it's "the" date I search).

 

So, question: how can I get the "real" creation date of all these videos on my PC? It exists: it's shown on the iPhone which took the video (wife's one), AND mine. Information is "lost" only on the PC, but I think it's a problem of either reading the "bad" metadata (I use FFMPEG to get it currently), or reading the bad file (maybe inside a iTunes file/database, but didn't find it, didn't find it in registry either).

 

Best solution is to find something working locally, through command line, to do it in batch mode. A GUI solution MAY be suitable if I can automate it with AutoIt (i.e. must not be a native Win32 application, not Java, .NET, GTK, QT and so on, OR must have an export feature). A web-based solution is not suitable, but may be less boring than examining each video one after the other to check manually the date.

 

Thanks in advance.

iPhone 6 Plus, iOS 10.0.2, Windows 10 x64

Posted on Oct 2, 2016 5:57 AM

Close

Q: Video metadata date is incorrect: set to sharing date and not creation date

  • All replies
  • Helpful answers

  • by zinacef,

    zinacef zinacef Oct 2, 2016 8:44 PM in response to Mac LAK
    Level 5 (4,851 points)
    Applications
    Oct 2, 2016 8:44 PM in response to Mac LAK

    In my opinion, the only way to correct this is through third-party software like this one.

  • by Mac LAK,

    Mac LAK Mac LAK Oct 2, 2016 10:31 PM in response to zinacef
    Level 1 (4 points)
    iCloud
    Oct 2, 2016 10:31 PM in response to zinacef

    Thanks for replying, but out of scope: I know how to EDIT metadata, either for photos and videos, this is not a problem for me. BTW, I'm on Windows, not Mac.

     

    My problem is to FIND this "real" date, and only for videos. I may have found what I need in a local SQlite database, but I'm stuck on an unusual timestamp (seems to be a number of seconds since a date in 2000, not an EPOCH or usual Mac timestamp, I don't have the exact time reference until now). Don't have access to that at work, I'll post further details this evening once at home.

     

    Any clue will be highly appreciated.

  • by Mac LAK,

    Mac LAK Mac LAK Oct 3, 2016 3:27 PM in response to zinacef
    Level 1 (4 points)
    iCloud
    Oct 3, 2016 3:27 PM in response to zinacef

    So... In database "%APPDATA%\Apple Computer\MediaStream\local.db" (SQLite database), I found the following informations thanks to "DB Browser for SQLite":

    • Each file is indexed through a hashed value (the filename without extension), but algorithm is unknown OR is calculated on more than just the file itself (hash is 168 bits long while common ones are 160 bits long, so there is 8 surnumerous bits). No part of the iCloud hash is found in others hashed values.
    • A field contains a timestamp which MAY be the one I search, named "dateCreated". Another one ("batchCreateDate") seems to be the sharing date. It seems to be a number of seconds since (2001/01/01, 00:00:00), in UTC time. It seems also that it works on videos, but I need to do a bit more research to be sure that it's really an UTC time constantly and never a local time (and in this case, I'll be forced to do a bit more work to adjust these timestamps to a local time, but I don't need further help on this particular point).
    • For videos, none of these two timestamps is matching the file's metadata, or there is a HUGE mess between local and UTC times (I suspect UTC dates displayed without the "UTC" next to it)... Again, I'll check further all this to solve this issue, including by doing some tests next to a NTP-synchronized clock. It's annoying, but it's still a detail (and a matter of one or two hours of error, not 15 days as I have some currently).
    • For curious people, the delta with an EPOCH timestamp is 978310800: add this constant to the iCloud timestamp and give it to any EPOCH conversion function to get the corresponding human date.

     

     

    So a big part of my problem is solved, but I still need help to identify the hashing algorithm in order to retrieve exactly the database line where I can find the date information.

     

     

    Tested hashing algorithms:  md5;LM;NTLM;sha1;sha256;sha384;sha512;md5(md5());MySQL4.1+;ripemd160;whirlpool; adler32;crc32;crc32b;fnv1a32;fnv1a64;fnv132;fnv164;gost;

    gost-crypto;haval128,3;haval128,4;haval128,5;haval160,3;haval160,4;haval160,5;ha val192,3;haval192,4;haval192,5;haval224,3;haval224,4;haval224,5;haval256,3;

    haval256,4;haval256,5;joaat;md2;md4;ripemd128;ripemd256;ripemd320;sha224;snefru; snefru256;tiger128,3;tiger128,4;tiger160,3;tiger160,4;tiger192,3;tiger192,4