IPhone – SQL to extract messages from backup

backupdata transferiphonemessages

We know that iPhones will backup Messages in a SQLlite-accessible database called (of course) 3d0d7e5fb2ce288813306e4d4636395e047a3d28.

So I'm browsing that using DB Browser for SQLlite, and it's not obvious what fields I want to best reconstruct to and from whom each message was sent.

So far I've gotten this, which at least gets the timestamp right, but which doesn't make it clear whether the message was sent or received on the phone in question:

SELECT datetime(message.date, 'unixepoch', '+31 years', '-6 hours'), handle.id, message.text
   FROM message, handle WHERE message.handle_id = handle.ROWID;

Best Answer

The following will add a column indicating whether the phone user was sending or receiving each message:

SELECT datetime(message.date, 'unixepoch', '+31 years', '-6 hours') as Timestamp, handle.id, message.text,
    case when message.is_from_me then 'From me' else 'To me' end as Sender
FROM message, handle WHERE message.handle_id = handle.ROWID;
Related Question