Thursday, July 31, 2014

How to remove chat history for a single contact from Skype?

This post is going to be a little strange compared with other posts on this blog.
In order to test some functionality on a web application I had to change the calendar date in future on my local machine.
In the while, I was using Skype and conversations were recording as of being on 8th of August (the date I experienced this). Then I return 'back from time' on current date. Well, from that time on Skype was behaving weird because it showed me conversations ordered by timespan ascending.
Well that is when you will got messed up :)

As Skype only allows you to delete all history conversation at once I had to search for a way to delete only those conversation that were 'in future' so Skype will show me last chat discussions on last row ;)

Searched around and found this solution.(it will require a little knowledge of SQL)

  1. Quit from Sype - required - otherwise you will have a sql error - database locked
  2. Go to C:\Users\PROFILE NAME\AppData\Roaming\Skype\SKYPE LOGIN ID and make a backup of main.db
  3. Go to http://sqlitebrowser.org/ and download the exe and install it on your machine.
  4. Go to: http://www.onlineconversion.com/unix_time.htm and get current timespan. - you will need it for sql query
  5. Open  SqLite and then go to OpenDatabase and choose the same database mentioned at 2)
  6. In order to make sure what you want to delete you could execute the next query   
  • SELECT * FROM "Messages" where timestamp > '1406808732'   (for timestamp you could put the one obtained at 4)  or if you want to check of an author use: author = 'authorId' ) authorId is skype Name
 If you are happy with the results that you see then execute same script with delete command:
  • DELETE FROM "Messages" where timestamp > '1406808732' --or use: (author = 'authorId')

Note that you could also filter the results by author/from_dispname or other columns of same Message table. AuthorIds could be taken from Contacts table and then skypename column .

Cheers.

7 comments:

  1. Superb
    Thank you so much

    ReplyDelete
  2. I tied this several times but it's not working

    SQL query executes successfully and after deleting the messages are not showing in select query but when I open the IM of the skype contact all messages are still showing there

    ReplyDelete
  3. Same problem with me too. All messages for a specific contact are still there. Not deleted.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hello, try this SQL excute for sure delete messages from specify contact
    As step 6:
    select * from "messages" where dialog_partner = 'your_contact_id_want_to_delete'
    If happy with result, now you could delete the history by below excute
    delete from "messages" where dialog_partner = 'your_contact_id_want_to_delete'

    After that, remember hit button WRITE CHANGES

    ReplyDelete
  7. Everything executes fine, local db is 'cleaned' but when I open the IM of the skype contact all messages are still showing there. Is it getting data from Skype cloud? If so, how can I sync that with local db?

    ReplyDelete