Here’s how you can access your entire iMessage history on your Mac

The message table indeed seems to hold all the saved iMessages.

It has a text field with the actual sent or received message, a date field (more on that below) and a handle id.

After a little exploration I found that the handle_id is a code for each phone number or Apple-id that you have had a conversation with.

In order to map the handle_id back to the Apple-id we can use a table in the database (appropriately) named handle and join on handle_id.

# get the handles to apple-id mapping tablehandles = pd.

read_sql_query("select * from handle", conn)# and join to the messages, on handle_idmessages.

rename(columns={'ROWID' : 'message_id'}, inplace = True)handles.

rename(columns={'id' : 'phone_number', 'ROWID': 'handle_id'}, inplace = True)merge_leve_1 = temp = pd.

merge(messages[['text', 'handle_id', 'date','is_sent', 'message_id']], handles[['handle_id', 'phone_number']], on ='handle_id', how='left')Adding a chat idSimilarly, the message table also includes a chat_id that maps each message back to unique chat.

This can be useful when doing analysis on chats with multiple people in them.

We can get the chat_id of each message by joining the message table with the (again, appropriately named) chat_message_join table on message_id.

# get the chat to message mappingchat_message_joins = pd.

read_sql_query("select * from chat_message_join", conn)# and join back to the merge_level_1 tabledf_messages = pd.

merge(merge_level_1, chat_message_joins[['chat_id', 'message_id']], on = 'message_id', how='left')Getting the dateThe message table also includes a date column and this was a little tricky for me to decode since it isn’t exactly in any format that is widely used in the industry.

Moreover, the way that this column is recorded is a little different in newer version of Mac OS X compared to older ones.

Credit to this stackoverflow page that helped me figure this out.

In Mac OS X versions before High Sierra (which is version 10.

13 and released in September 2017), the date column is an epoch type but, unlike the standard of counting the seconds from 1970–01–01, it is counting the seconds from 2001–01–01.

In order to convert that type into a data field we can actually comprehend we can use a command while querying the message table to create a new field (we will call it date_utc, since it is giving a UTC timezone date as a result) based on the date field.

# convert 2001-01-01 epoch time into a timestamp# Mac OS X versions before High Sierradatetime(message.

date + strftime("%s", "2001-01-01") ,"unixepoch","localtime")# how to use that in the SQL querymessages = pd.

read_sql_query("select *, datetime(message.

date + strftime("%s", "2001-01-01") ,"unixepoch","localtime") as date_uct from message", conn)In Mac OS X High Sierra and above, it’s the same thing but the date format is now much more granular: it is in nano-second level.

So now we need to divide by 1,000,000,000 before we apply the same code snippet we applied above.

# convert 2001-01-01 epoch time into a timestamp# Mac OS X versions after High Sierradatetime(message.

date/1000000000 + strftime("%s", "2001-01-01") ,"unixepoch","localtime")# how to use that in the SQL querymessages = pd.

read_sql_query("select *, datetime(message.

date/1000000000 + strftime("%s", "2001-01-01") ,"unixepoch","localtime") as date_uct from message", conn)Putting it all togetherYou can find the notebook here with all the code in order for you to extract your iMessages from your laptop and start analyzing!It should only take a few minutes and by the end of it you should have a basic history of your iMessage data that includes the phone number (or email), the text, a unique chat for each unique group of people you had a chat with and the timestamp (in UTC timezone) of each message sent.

You can actually find more data in the database such as details if the message was delivered and read as well as attachments.

I’m not touching on those attributes on this post.

Happy reading your messages!Was this helpful?.Let me know if things were not clear.

.. More details

Leave a Reply