iMessages Database

gistfile1.txt

How to connect
 > sqlite3 ~/Library/Messages/chat.db

Helpful Commands
 sqlite> .help
 sqlite> .tables
 sqlite> .headers off
 sqlite> .headers on
 sqlite> .mode line
 sqlite> .mode list
 sqlite> .schema handle
 sqlite> .schema message
 
Find a specific friend by phone number (find their handle id)
 sqlite> /* example: look for friend's from the 802 area code */
 sqlite> SELECT ROWID, id
         FROM handle
         WHERE id like '+1802%';
 
Find message history with a specific friend
 sqlite> /*  let's assume the ROWID from the previous query was 41 */
 sqlite> .mode list
 sqlite> SELECT ROWID, date, is_from_me, text
         FROM message
         WHERE handle_id=41
         ORDER BY date;
 
Find a specific message from a friend
 sqlite> /* look at the ROWID of this query.  */
 sqlite> SELECT ROWID, date, is_from_me, text
         FROM message
         WHERE handle_id=41 and text like '%something they said%'
         ORDER BY date;

 sqlite> /* Let's assume we found our message at ROWID=7005 */
 sqlite> /* then we can grab the next 10 messages from that conversation */
 sqlite> SELECT ROWID, date, is_from_me, text
         FROM message 
         WHERE handle_id = 41 and ROWID >= 7005
         ORDER BY date
         LIMIT 10;
 
Readable Date Format
 SELECT ROWID, datetime(date + strftime('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') as date, is_from_me, text
 FROM message
 WHERE handle_id in (1, 27)
 ORDER BY date;