Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 ? –  Lamb Dec 5 '12 at 16:52

1 Answer 1

up vote 3 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.