Query your firefox browsing history with sqlite

Background

I was search for a number that I had rang from an advertisement I seen on the internet. A few months later I needed that number. I went to the url and found the ad had expired. There was no number available. I have an itemized phone bill but the number could be any where with in the last 6 months. I needed to narrow down the search.

Information that I knew:

• The url of the advertisement
• I rang the number around lunchtime.

Firefox history

I checked my firefox browser history. I thought that if i knew the date I visited the advertisement it would reduce the number of telephone numbers I had to choose from. The browser history page was not detailed enough for that.

2 issues with the firefox history browser

• It organizes the website you visited into what month you visited it if it older than a month. There is no column for exact date or time of the visit.
• When using the searchbox the url would appear in the results but it would not tell you what month,date or time you visited the url.

Firefox history database

In your home directory firefox keeps all the history in a sqlite file.

~/.mozilla/firefox/<random directory>/places.sqlite

To find your database run this command

find ~/.mozilla/firefox/ -name places.sqlite

In the moz_places table it has an epoch in microseconds of the exact time you last visited the website. Unfortunately if you visited the site more recently you only have that date. Firefox does not keep a record of every time you visited a url. By chance I visited this url on 2 different computers so one computer still had my first visit time while the other had the time that I visited the expired advertisement.

I was then able to get the exact url and query the table. I converted from microseconds to a date in one query.

select datetime((select last_visit_date from moz_places where url='<EXACT URL>')/1000000,'unixepoch', 'localtime');

I found the date I visited the url. I was able to see on my phone bill the next day that I rang a number I did not know. I rang the number and it turned out to be the person I was looking for. The query helped me narrow down my search.