Search skype history across chats

October 14 2013 : posted under skype

I find a lot of clients use skype

For me the biggest frustration is the limited search

But it turns out the data is stored using sqlite - and so you can search using sql directly on the sqlite db files.

https://gist.github.com/seanburlington/740d136e58d8b87d057f

#!/bin/bash

export username=skype.name
sqlite3 -column ~/.Skype/$username/main.db \
"SELECT displayname, from_dispname, datetime(Messages.timestamp, 'unixepoch') as date, body_xml
             FROM Messages, Conversations
             WHERE Conversations.id=Messages.convo_id and body_xml like '%$searchterm%'
             ORDER BY Messages.timestamp;"

or for all your chats from a given day

https://gist.github.com/seanburlington/85e282537f6f2f631414

export username=skype.name
export thedate=$(date +%Y-%m-%d)
sqlite3 ~/.Skype/${username}/main.db \
"SELECT displayname, datetime(Messages.timestamp, 'unixepoch') as date, body_xml
           FROM Messages, Conversations
           WHERE Conversations.id=Messages.convo_id and date(Messages.timestamp, 'unixepoch') == '${thedate}' and author='${username}'
           ORDER BY Messages.timestamp;"