Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
So, you want to see a list of all visits to that site ordered by visiting time ? – Ankit Mishra Dec 5 '12 at 16:52
up vote 4 down vote accepted

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.

share|improve this answer
    
Thank you! This is exactly what I was looking for. – sammyg Dec 6 '12 at 12:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .