Avatar for Username

Search Support

Avoid support scams. We will never ask you to call or text a phone number or share personal information. Please report suspicious activity using the “Report Abuse” option.

Learn More

This thread was archived. Please ask a new question if you need help.

Query places.sqlite for first visit of specific site?

  • 1 reply
  • 1 has this problem
  • 3 views
  • Last reply by cor-el

Dorn
Dorn
more options

A few years ago someone asked a similar question here https://support.mozilla.org/en-US/questions/937585

Currently, I am trying to use SQLite Manager extension for Firefox to try and find a very specific url which I visited around April 15th of last year, which I can remember through many details, but cannot find in my history or remember the name of. I know for a fact that it exists though.

I tried using this query, developed from the support thread above, to find what I was looking for

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 '%tumblr.com%' ORDER BY Time DESC

However, doing so does not provide any dates, it only arranges all of the visits in chronological order. Which is good, but upon searching for the gap in my history using the context of what my normal history shows from that day, it would seem that the blog I am searching for isn't in the location it should be.

Currently my hope is to somehow get the Manager to show me the specific dates I visited sites, so I can arrange the list that way and hopefully pull the blog back into a position where I will recognize it by the date I visited it. Otherwise I would like to find a solution, any solution, to figure out what sites I visited that day to find the blog in question, as I do not expect to ever find it using context clues.

A few years ago someone asked a similar question here https://support.mozilla.org/en-US/questions/937585 Currently, I am trying to use SQLite Manager extension for Firefox to try and find a very specific url which I visited around April 15th of last year, which I can remember through many details, but cannot find in my history or remember the name of. I know for a fact that it exists though. I tried using this query, developed from the support thread above, to find what I was looking for 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 '%tumblr.com%' ORDER BY Time DESC However, doing so does not provide any dates, it only arranges all of the visits in chronological order. Which is good, but upon searching for the gap in my history using the context of what my normal history shows from that day, it would seem that the blog I am searching for isn't in the location it should be. Currently my hope is to somehow get the Manager to show me the specific dates I visited sites, so I can arrange the list that way and hopefully pull the blog back into a position where I will recognize it by the date I visited it. Otherwise I would like to find a solution, any solution, to figure out what sites I visited that day to find the blog in question, as I do not expect to ever find it using context clues.

All Replies (1)

cor-el
cor-el
  • Moderator
  • Top 10 Contributor
more options

I think that a query like this should still work. 

SELECT datetime(visit_date/1000000,'unixepoch','localtime') AS visit_date, url, title, visit_count
FROM moz_places, moz_historyvisits
WHERE moz_places.id = moz_historyvisits.place_id AND url LIKE '%mozilla.org%' AND visit_date BETWEEN strftime('%s','YYYY-MM-DD HH:MM:SS')*1000000 AND strftime('%s','YYYY-MM-DD HH:MM:SS')*1000000
ORDER BY url,visit_date DESC

You can also try to full search for a specific time frame: 

WHERE moz_places.id = moz_historyvisits.place_id  AND visit_date BETWEEN strftime('%s','YYYY-MM-DD HH:MM:SS')*1000000 AND strftime('%s','YYYY-MM-DD HH:MM:SS')*1000000

JavaScript to create a specific place query: 

prompt("Place query:","place:sort=4&type=1&beginTime="+(1E3*Date.parse(prompt('Start Date (mm/dd/yyyy 0:00:00)',Date())))+"&endTime="+(1E3*Date.parse(prompt('End Date (mm/dd/yyyy 0:00:00)',Date()))));

Modified by cor-el