I wrote a number of SQL queries for browsing my browsing history. In this snippet, I show how to amend them to filter the results by date. Google Chrome selects a slightly non-standard time storage format (microseconds since January 1, 1601 UTC), hence the need for this query stanza.
To run these queries, first make a copy of your browser history and open it in sqlite3.
cp ~/Library/Application\ Support/Google/Chrome/Default/History ~/my-history
sqlite3 ~/my-history
Google Search History for Specific Date
Here’s the query we had from before, for accessing our Google Search history.
select DISTINCT REPLACE(urls.title, " - Google Search", "")
from urls
where urls.url like "%www.google.com/search%"
limit 50;
To filtering by a specific date, we just add.
AND
datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch')
LIKE '2019-12-25%'
What was I searching for on Christmas last year?
select DISTINCT REPLACE(urls.title, " - Google Search", "")
from urls
where urls.url like "%www.google.com/search%"
and datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch')
LIKE '2019-12-25%'
limit 50;
YouTube Videos Watched Today
To query for results from today, we can do the following.
select DISTINCT REPLACE(urls.title, " - YouTube", ""), urls.url, datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') as d
from urls
where urls.url like "%www.youtube.com/watch%"
and datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch')
LIKE (date('now', 'localtime') || '%')
limit 50;
Note the use of localtime to account for the timezone. || is used here for string concatenation.
Arxiv Papers Visited Last Month
select DISTINCT urls.title, REPLACE(REPLACE(urls.url, ".pdf", ""), "/pdf/", "/abs/")
from urls
where (urls.url like "https://arxiv.org/abs%"
or urls.url like "https://arxiv.org/pdf%")
and datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch')
LIKE (substr(date('now', 'localtime', '-1 month'), 0, 9) || '%')
limit 50;
Here you can see that date gives flexibility in adjusting the date you are requesting. substr is used to transform 2020-03-20 to 2020-03-, to match any date in the month.
HackerNews Links Followed in the Last Week
select DISTINCT urls.title, urls.url
from visits
join visits as previous_visits on visits.from_visit == previous_visits.id
join urls on urls.id == visits.url
join urls as previous_urls on previous_urls.id == previous_visits.url
where previous_urls.url like "https://news.ycombinator.com%"
and urls.url not like "https://news.ycombinator.com%"
and datetime(visits.visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch')
> date('now', 'localtime', '-7 day')
limit 50;
For this query, we directly compare the result of datetime and date.
sqlite.org/lang_datefunc.html has more useful information about writing queries manipulating dates and times.
For more query ideas, here’s the link back to the browser history queries snippet.