Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLite SQL Query for iPhone Text Message Backup
-- 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
@aaronhoffman

This comment has been minimized.

Copy link
Owner Author

aaronhoffman commented Feb 22, 2017

@SethRobertson

This comment has been minimized.

Copy link

SethRobertson commented Dec 26, 2017

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

@aaronhoffman

This comment has been minimized.

Copy link
Owner Author

aaronhoffman commented Dec 28, 2017

Thanks @SethRobertson, I've updated the gist

@djunqua

This comment has been minimized.

Copy link

djunqua commented Aug 2, 2018

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;

@ScurryPots

This comment has been minimized.

Copy link

ScurryPots commented Jan 23, 2020

This was very useful. Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.