Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
SQLite SQL Query for iPhone Text Message Backup
-- more info
,coalesce(m.cache_roomnames, ThreadId
,m.is_from_me IsFromMe
,case when m.is_from_me = 1 then m.account
else end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
else coalesce(, end as ToPhoneNumber
,m.service Service
/*,datetime( + 978307200, 'unixepoch', 'localtime') as TextDate -- date stored as ticks since 2001-01-01 */
,datetime(( / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate /* after iOS11 date needs to be / 1000000000 */
,m.text MessageText
,c.display_name RoomName
message as m
left join handle as h on m.handle_id = h.rowid
left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */
left join chat_handle_join as ch on c.rowid = ch.chat_id
left join handle as h2 on ch.handle_id = h2.rowid
-- try to eliminate duplicates due to non-unique message.cache_roomnames/chat.room_name
(h2.service is null or m.service = h2.service)
order by
2 -- ThreadId

I had to divide / 1000000000 in order for this to work. Looks like it was converted to nanoseconds at some point.


aaronhoffman commented Dec 28, 2017

Thanks @SethRobertson, I've updated the gist

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