I'm trying to extract the chat history between myself and my SO on Skype for OSX. I've managed to pull out the information and strip out the unnecessary XML formatting with the following command:

sqlite3 /path/to/main.db "SELECT author,timestamp, body_xml FROM messages WHERE dialog_partner = 'so_username'" | sed -e 's/<[^>]*>//g' - > output.txt

This seems fine, but what I notice is that the timestamps in the output file are in some bizarre non-human readable format. Is there a way I can parse these?

  • 1
    Why don't you post some timestamp samples here so people can take a look, instead of requiring them to duplicate your work?
    – Karan
    Apr 7, 2013 at 14:01

2 Answers 2


Ok, figured it out. Here's the query:

SELECT author, from_dispname, datetime(timestamp, 'unixepoch') as date, body_xml FROM Messages where dialog_partner = 'sousername' ORDER BY timestamp;

As per Viewing the full Skype chat history.


Thanks. It works in Database browser under XP: SELECT COUNT() FROM (SELECT rowid, FROM SMSes ORDER BY rowid ASC); SELECT rowid,datetime(timestamp, 'unixepoch') as date, * FROM SMSes ORDER BY date ASC LIMIT 0, 50000;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.