Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Show recent file attachments from Message.app's chat DB
-- sqlite3 ~/Library/Messages/chat.db < recent-attachments.sql
-- dates in the DB are NSDate. NSDate.h says: NSTimeIntervalSince1970 978328800
SELECT Datetime(created_date + 978328800, 'unixepoch', 'localtime') 'created',
mime_type,
Printf("%.2f", Cast (total_bytes AS FLOAT) / 1000 / 1000) 'megabytes',
handle.id 'buddy',
filename
FROM attachment
JOIN message_attachment_join
ON message_attachment_join.attachment_id = attachment.rowid
JOIN chat_message_join
ON chat_message_join.message_id = message_attachment_join.message_id
JOIN chat_handle_join
ON chat_handle_join.chat_id = chat_message_join.chat_id
JOIN handle
ON handle.rowid = chat_handle_join.handle_id
WHERE Strftime('%s', 'now') - ( created_date + 978328800 ) <
( 60 * 60 * 24 * 7 ); -- 7 days
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.