Note: this was a draft article that has been sitting here forever, and I don’t think I actually published it anywhere (if I did I can’t find it) and a post on reddit reminded me of it, so I figured I’d throw it up here.
Problem
I’ve been using the iPhone app Everyday since 2011, basically well, every day and am wanting to move away from it as it hasn’t received any updates and seems abandoned. Everyday has an export feature, but for the life of me I couldn’t get it to export more than a year without crashing. So to get the 6 years of selfies (1,950 photos total as I find later) is a challenge.
I found a new app called Close-Up which seems like a decent replacement and has http://twitter.com/closeupapp. I talked to the author of the app and he said that:
- The app will pull in photos in a dropbox folder to backfill images (some apps can do this but one photo at a time, and with 6 years of photos I’m not going to do that) if they’re named properly.
- The naming scheme is very simple, just the unix epoch date with a JPG extension. So 1505435106.jpg for a selfie taken right now.
The challenge is to get these files out and rename them properly. It took a bit of work and I thought it might be interesting to write out how it was used.
- Backup iPhone
- Use iExplorer to explorer the backup and found the Everyday app
- From there I was able to copy the SQLite (database) files and the photos onto my filesystem.
- Unfortunately the photos were all named with random strings, as you can see here.
- Even worse, the date taken information was stripped out! Looking for data information using exiftoolshowed nothing:
- Compared to a normal image and the same command:
- So I’m not able to simply rename the files based on the date the photos were taken (through Lightroom or a script of some sort).
- Luckily the sqlite database (the data storage that the app uses) has the data needed (kinda, more on that in a second). I loaded up SQLite Browser and took a look at the database schema.
- The ZPHOTO table holds the time taken (which is missing from the EXIF data) and the random identifier that the files were named as. So, I have:
- ZIDENTIFIER (a string)
- ZCREATED (a timestamp)
- The first big wrench here was the timestamp in SQLite is different than the normal UNIX “epoch” timestamp. I was able to solve this with a bit of help where I found that I had to add 978307200 to the timestamp to convert from a epoch starting at 2001-01-01 to one starting at 1970-01-01.
- Using this I confirmed this worked by using this SQL
select ZIDENTIFIER, strftime(‘%s (%Y-%m-%d)’, datetime(ZCREATED+978307200, ‘unixepoch’,’localtime’)) from ZPHOTO;
- This shows the right dates next to the identifiers.
- I was able to confirm these were right by matching up the file with the name from the identifier column with the calculated date. Perfect!
- Combining these two bits of information will give me most of what I need, but first I need to get the data out of the SQLite browser, which I couldn’t see how to export the data out of. So I turned my attention to the command line SQL tool, enabling output to a file and then executing the SQL needed.
https://stackoverflow.com/questions/6076984/how-do-i-save-the-result-of-a-query-as-a-csv-file
SQL Used:
select ZIDENTIFIER, strftime(‘%s (%Y-%m-%d)’, datetime(ZCREATED+978307200, ‘unixepoch’,’localtime’)) from ZPHOTO;
Ok, now I have a CSV file with the identifier and a date. The final result of the script is a folder of photos renamed so their filename is the unix epoch matching when they were taken. To do this I whipped out a little perl script.
(Actually the first thing I had to do was convert the file from DOS format to UNIX as it output in DOS format with CRLF).
The script probably isn’t nearly as efficient as it could be, and honestly probably could have been done in either a single line or with shell commands, but this is how I did it. The abbreviated version of the script is it opens up the CSV file and reads a line.
73BACA92-6AFA-4D23-846E-44BBEB62F70E,1315648867
Splits the line at the point of the comma into $oldname (the identifier) and $newname (the number). Then it simply constructs a command that turns into:
mv ptest/73BACA92-6AFA-4D23-846E-44BBEB62F70E.jpg ptest/new/1315648867.jpg
To rename (via the unix ‘mv’ command) the photos sitting in the test folder.
End result, a lovely list of files, all renamed properly!
These I then put into Dropbox in the right folder and voilà, the new app started pulling them in and syncing. Success!