Skype: Decode time stamps

Question

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?

Answer 1

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.

Answer 2

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;