SQLite SQL Query for iPhone Text Message Backup

iphone-text-message-sqlite.sql

-- more info http://aaron-hoffman.blogspot.com/2017/02/iphone-text-message-sqlite-sql-query.html
select
 m.rowid
,coalesce(m.cache_roomnames, h.id) ThreadId
,m.is_from_me IsFromMe
,case when m.is_from_me = 1 then m.account
 else h.id end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
 else coalesce(h2.id, h.id) end as ToPhoneNumber
,m.service Service

/*,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate -- date stored as ticks since 2001-01-01 */
,datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate /* after iOS11 date needs to be / 1000000000 */

,m.text MessageText

,c.display_name RoomName

from
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

where
-- 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
,m.date

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

Thanks @SethRobertson, I've updated the gist

Thanks for this post.
here is a proposition of request allowing to display either the text message or the name of the image.

select m.rowid
,coalesce(m.cache_roomnames, h.id) ThreadId
,m.is_from_me IsFromMe
,case when m.is_from_me = 1 then m.account
else h.id end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
else coalesce(h2.id, h.id) end as ToPhoneNumber
,m.service Service
,datetime((m.date) + 978307200, 'unixepoch', 'localtime') as TextDate
,case when m.text is null then "file : "||a.filename
else m.text end as MessageText
,c.display_name RoomName
from
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
left join chat_handle_join as ch on c.rowid = ch.chat_id
left join handle as h2 on ch.handle_id = h2.rowid
left join message_attachment_join as ma on ma.message_id = m.rowid
left join attachment as a on ma.attachment_id = a.rowid
where
(h2.service is null or m.service = h2.service)
order by
m.rowid -- ThreadId
,m.date;

This was very useful. Thank you.

How would I limit the results to one specific chat?

I'm new ro GitHub so I apologize in advance. Thank you for these queries as they have been extremely helpful. How can I link a sent png in attachment to the recipient? I'm having difficulties mapping.

This has been incredibly helpful for me and I thank you for providing it. I wonder if anyone has figured out how to incorporate the new "Recently Deleted" messages into an SQL query? I can see in the raw data that I've deleted items but I can't find the message content. Recovering messages that were deleted within 30 day was a new feature of OS16.