explore iChats on Mac OS

ichat.md

Accessing iChat's on MacOS

sqlite3 ~/Library/Messages/chat.db

notable tables:

• attachment – metadata and storage location
• chat – a collection of your messages (both direct and group)
• handle – metadata about chats
• message – all messages sent and received

Queries

All Messages

SELECT
    datetime (message.date / 1000000000 + strftime ("%s", "2001-01-01"), "unixepoch", "localtime") AS message_date,
    message.text,
    message.is_from_me,
    chat.chat_identifier
FROM
    chat
    JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
    JOIN message ON chat_message_join.message_id = message. "ROWID"
ORDER BY
    message_date ASC;

Find a message that matches some text

SELECT
    datetime (message.date / 1000000000 + strftime ("%s", "2001-01-01"), "unixepoch", "localtime") AS message_date,
    message.text,
    message.is_from_me,
    chat.chat_identifier
FROM
    chat
    JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
    JOIN message ON chat_message_join.message_id = message. "ROWID"
WHERE
    message.text like '%😂%'
ORDER BY
    message_date ASC;

Total messages across all chats

SELECT
    chat.chat_identifier,
    count(chat.chat_identifier) AS message_count
FROM
    chat
    JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
    JOIN message ON chat_message_join.message_id = message. "ROWID"
GROUP BY
    chat.chat_identifier
ORDER BY
    message_count DESC;

Messages between you and another person

SELECT
    count(*) AS message_count,
    sum(length(message.text)) AS character_count,
    sum(length(message.text)) / 3000 AS estimated_page_count,
    message.is_from_me,
    chat_id
FROM
    chat
    JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
    JOIN message ON chat_message_join.message_id = message. "ROWID"
WHERE
    chat.chat_identifier = '<phone number or ichat handle>'
GROUP BY
    message.is_from_me;

Export as text

Select all Handles

SELECT * FROM handle;

Create output file

.output filename.txt

Select conversations, replacing 12345 with the ID of the conversation you're exporting.

SELECT 
    handle_id, 
    date, 
    datetime(date / 1000000000,'unixepoch','31 years'), 
    T1.is_from_me,
    text 
FROM message T1 
INNER JOIN 
    chat_message_join T2 ON T2.chat_id = 12345
    AND T1.ROWID = T2.message_id 
ORDER BY T1.date;

Creates a text file, filename.txt, containing this conversation in chronological order. You can run .schema message to see the full list of columns if you need to save more data - add a comma and the name of the column onto the SELECT statement.