How to write an SQL query for SQLite Manager for Firefox places.sqlite?

Question

I am using SQLite Manager which is an extension for Firefox. I use this to inspect the places.sqlite database. I am trying to find the earliest visit date for a specific URL in the Firefox history.

The following query does just that.

SELECT url, title, visit_count, datetime(first_visit/1000000,'unixepoch') AS EarliestVisit, datetime(last_visit_date/1000000,'unixepoch') AS LatestVisit
FROM moz_places INNER JOIN 
 (SELECT place_id, MIN(visit_date) AS first_visit 
  FROM moz_historyvisits 
  GROUP BY place_id) AS FirstVisits 
 ON FirstVisits.place_id = moz_places.id
WHERE url LIKE 'http://www.domain.com/?id=1234'
ORDER BY url

The result given shows the latest visit as 2012-12-05 11:35 and the earliest visit as 2012-12-02 17:29. But the given earliest visit is too far back in time. I would like to see the visiting times after that one.

How can I rewrite this query so that I get all the dates and times this specific URL has been visited on?

The original issue is that Firefox is no longer showing the earliest visit to a website when viewing history in the Library.

Answer 1

Use this code to get a list of all the dates and times this specific URL has been visited on:

SELECT url, datetime(visit_date/1000000,'unixepoch') AS Time
FROM moz_historyvisits, moz_places
WHERE
moz_historyvisits.place_id=moz_places.id
AND
url LIKE 'http://www.domain.com/?id=1234'
ORDER BY Time DESC

Latest visit will be displayed at top you can invert it by replacing DESC by ASC.