The visualizations are not optimized for mobile devices. Use a desktop browser instead for greate performance.

I like iMessage and Google Hangout (previous known as GTalk) more than WeChat for at least one simple reason: I can manipulate MY DATA at will. WeChat has its own design philosophy that contradicts mine fundamentally - 1, your data is only in your local disk and the only support is to temporarily save it for seven days in the cloud. 2, logging using computer requires scanning QR code, which is ridiculously hard and inconvenient when at class. Though I have tried to avoid it, many of my friends are using it and I got isolated for being not involved. So I have an account if you ask, but whenever possible, Google Hangout (then iMessage) is probably a better way to find me.

This post is a demonstration of my claim of “manipulating at will”. The visualization is borrowed from Crossfilter. For anonymity, I am using person identification rather than person names but I believe it’s not hard to figure out the specific person if you are him/her (like person 3). And if you limit the time range to be this spring semester afternoon time, you will see the few people that I am keeping in touch with.

For now the data only contains iMessage data. Hangout support will be added later :)

Message time

Here I document the way you can get the desired csv file:

sqlite3 ~/Library/Messages/chat.db
sqlite> .mode csv
sqlite> .output chat.csv
sqlite> select distinct date + 978307165 as date, length(text), chat_id
   ...> from message, chat_message_join
   ...> where message.rowid = chat_message_join.message_id;
sqlite> .output stdout

Since the chat.csv file won’t contain header, you will need to manually add the header. Besides, the magic number 978307165 may be only unique. You will have to find your own.