How to sort contacts by creation date or modification date in iOS Contacts or OS X Contacts 7.x?

Question

Is there a way to view or export the date a contact was created or modified in iOS 7 and/or the Contacts app (version 7.1) when viewed from a Mac?

This one datapoint would enormously aid deduping and syncing, even in a more manual way.

Answer 1

After digging a little bit in Contacts' DB here is what I found.

As already said by @grgarside, the DB is in

~/Library/Application\ Support/Address\ Book/Sources/<source-ID>/AddressBook-v22.abcddb

Where <source-ID> is most likely the most recent modified directory.

AddressBook-v22.abcddb is a SQLite DB file with 24 tables (in my case). The most important one is ZABCDRECORD which holds (among others) the firstname, lastname and the creation date of the contact (but also modification etc).

The 2 interesting columns in your case are :

• ZCREATIONDATE
• ZMODIFICATIONDATE

As I found out recently, the base date for these 2 columns is 01-01-2001. The columns ZMODIFICATIONDATEYEARLESS and ZCREATIONDATEYEARLESS use the 1st January of creation year as the base date.

Using sqlite you can sort it like that :

sqlite3 AddressBook-v22.abcddb "select ZFirstName, ZLastName from ZABCDRECORD order by ZMODIFICATIONDATE"

It will output FirstName/LastName sorted by modification date.

Answer 2

The Contacts database is stored in a single SQLite3 database. You can find yours here…

~/Library/Application\ Support/AddressBook/Sources/<source-ID>/AddressBook-v22.abcddb

This can be exported using

sqlite3 AddressBook-v22.abcddb .dump > ~/Desktop/export

Since the result is a single file and created/modified dates don't appear to be stored in my database, the only way to sort by date is by comparing various versions of the file.

You can use Time Machine to grab older versions of the file and FileMerge (a developer tool) or other comparison tool such as Kaleidoscope to compare the result.

If you want to look through your iOS database, either jailbreak or sync your contacts to your Mac.

Answer 3

Try something like that if you want to sort by Create date and see the date:

1. Open Terminal
2. Navigate to AddressBook-v22.abcddb
3. Sqlite3 AddressBook-v22.abcddb "select datetime(ZCREATIONDATE,'unixepoch'), ZFirstName, ZLastName from ZABCDRECORD order by ZCREATIONDATE"

Answer 4

Adding onto the answers here (I still don't know how to add a link to a comment) - if you wish to do this once, then the shell scripts would suffice. If you wish to do it on an ongoing basis, you might think about using a product - I use SQLiteManager, but it's pricey, except when on sale. There are a number of free and web-based SQLite managers out there. These apps (like SQLManager), should have the ability to order by any column. I find this very useful, not just for contacts, but any SQLite database. Here is an example of a SQLite Firefox extension.

For reference, if you want to see SQLite databases that apps use, find the app in Finder (~/Music/iTunes/Mobile \ Applications, copy it to your desktop. Change the suffix to zip, and double-click. Be careful. Control-click the .app file, and if the developer didn't mind, the sqlite file will be at the top level. I use this facility to test my apps when they go live, or when the OS changes.

If you choose to stay with scripts, maybe someone else can provide the specifics, but I believe you could create a script that not only copied the contacts - I suggest - read only - but then paste them into Numbers or equivalent. Then, you would also have the ability to sort by column.

Answer 5

If you just want a quick way to view contacts sorted by creation date, there are a few apps that do that. For example, https://itunes.apple.com/tc/app/recent-contacts/id541831736?mt=8.