Skype SqlLite Database Queries

skype-query.sql

cd /Users/corne/Library/Application\ Support/Skype/corne.aussems/
cp main.db main-bak.db
sqlite3 main-bak.db
/** pretty print **/
sqlite3 -separator $'\t' -header -cmd '.mode columns' main-bak.db ;

/** You want to know what you wrote in each Conversation last 2 days **/
SELECT displayname,from_dispname, datetime(Messages.timestamp, 'unixepoch') AS date, body_xml 
FROM Messages LEFT JOIN Conversations on Messages.chatname = Conversations.identity 
WHERE Messages.timestamp BETWEEN strftime('%s','now', '-2 days') AND strftime('%s','now') 
AND author = 'corne.aussems'
group  by displayname, date
ORDER BY date ;

/** You want to know what you wrote sequentially for last 2 days **/
SELECT datetime(Messages.timestamp, 'unixepoch') AS date, displayname, from_dispname, substr(body_xml,0,50) 
FROM Messages LEFT JOIN Conversations on Messages.chatname = Conversations.identity 
WHERE Messages.timestamp BETWEEN strftime('%s','now', '-2 days') AND strftime('%s','now') 
AND author = 'corne.aussems'
ORDER BY date ;