Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Query to extract iOS Messages from backup
-- Do a backup to iTunes and open "%APPDATA%\Apple Computer\MobileSync\Backup\*\3d0d7e5fb2ce288813306e4d4636395e047a3d28" as a SQLite database
SELECT chat.chat_identifier, message.is_from_me, datetime(message.date + 978307201, 'unixepoch') as date, message.text
FROM chat
JOIN chat_message_join on chat.ROWID = chat_message_join.chat_id
JOIN message on message.ROWID = chat_message_join.message_id
order by message.date
@llaagg

This comment has been minimized.

Copy link

@llaagg llaagg commented Feb 26, 2021

Not sure why, but this formatting of date didn't work for me.
Instead i was successful with:
datetime(substr(date, 1, 9) + 978307200, 'unixepoch', 'localtime') as f_date

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment