Skip to content

Instantly share code, notes, and snippets.

@duncansmart

duncansmart/ios-sms.sql

Created September 25, 2014 12:29
Show Gist options
  • Star 19 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save duncansmart/9d433cc0bca82f14d249 to your computer and use it in GitHub Desktop.
Save duncansmart/9d433cc0bca82f14d249 to your computer and use it in GitHub Desktop.
Download ZIP
Query to extract iOS Messages from backup
Raw
ios-sms.sql
-- 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
Copy link

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