Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 '13 at 14:01
up vote 0 down vote accepted

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 skype chat history.

share|improve this answer

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;

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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