-
-
Save aaronhoffman/cc7ee127f00b6b5462fa7fc742c23d4f to your computer and use it in GitHub Desktop.
| -- 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.
How would I limit the results to one specific chat?
Find a message from the conversation in the message table, grab the ROWID, for example 1234
You have to tie it in with the chat_message_join table with:
SELECT cmj.chat_id FROM chat_message_join cmj WHERE cmj.message_id=1234;
Now that you have the chat_id:
SELECT
m.rowid AS MessageRowID,
CASE
WHEN m.is_from_me = 1 THEN 'Me'
ELSE h.id
END AS FromPhoneNumber,
m.is_from_me AS IsFromMe,
m.service AS Service,
datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as MessageDateDate,
CASE
WHEN m.text IS NULL THEN 'file : ' || a.filename
ELSE m.text
END AS MessageText
FROM chat_message_join cmj
JOIN message m
ON cmj.message_id = m.rowid
LEFT JOIN handle h
ON m.handle_id = h.rowid
LEFT JOIN message_attachment_join maj
ON maj.message_id = m.rowid
LEFT JOIN attachment a
ON maj.attachment_id = a.rowid
WHERE cmj.chat_id = 54321 /* Put your chat_id here */
ORDER BY m.date;
blog post for more info: http://aaron-hoffman.blogspot.com/2017/02/iphone-text-message-sqlite-sql-query.html