Earlier this month, I wrote a post about a shell one-liner I use to view "what files and projects have I touched on this computer?" over the past month or more. I decided to write about another strategy I use to remember "what was I doing X months ago?" that I think will work for others who keep regularly-scheduled and easily-accessible backups.
If you do backups regularly, chances are you will have your old browser histories somewhere in those backups. For better or worse, in 2022, your browser history is a record of your activities both online and locally (e.g. when using Stack Exchange) throughout the past few weeks. Sometimes I need to know "what was I doing a few weeks or months ago?", and I haven't figured out a technique to keep this information in my head :). Instead, I can and do use my old browser histories to answer this question.
How far browser history goes back seems to be browser-dependent. Idk about Chrome offhand, but Opera seems to cap your browser history at ~10,000 entries by default, and Firefox by default chooses the bound for you (but it's much higher- my history goes back to Feb 2022; I switched to Firefox in May 2022). However, if you do backups regularly, chances are you will have your old browser histories somewhere in those backups. In effect, it's possible to have a record of your browsing history going back literal years.
Both Chrome-like browsers and Firefox use the SQLite library to store browser histories; Idk about Safari offhand. So, if a browser's built-in history viewer isn't sufficient for your needs, you can run SQLite queries against the history file to find the information you want. I used to use Python for this, but I found something better recently.
A lot of people don't seem to know this, but the SQLite library comes with a command-line application driver called, appropriately enough, sqlite3. Using the wonderful SQLite Tutorial website, and a code snippet for converting Webkit timestamps to a human readable format, here is an example query I ran recently I used to jog my memory about "when did I download an Ubuntu distribution to resize my partitions?":
sqlite3 -readonly /path/to/backup/in/nearby/timeframe/AppData/Roaming/Opera\ Software/Opera\ Stable/History "SELECT title,url,datetime((last_visit_time/1000000)-11644473600, 'unixepoch', 'localtime') AS last_visit_time FROM urls WHERE title GLOB '*Ubuntu*'"
If I didn't have an approximate timeframe, I could use the following shell snippet, whose output is ready to be piped into other commands, to help narrow it down:
for i in /path/to/backups/*/AppData/Roaming/Opera\ Software/Opera\ Stable/History; do sqlite3 -readonly "$i" "SELECT title,url,datetime((last_visit_time/1000000)-11644473600, 'unixepoch', 'localtime') AS last_visit_time FROM urls WHERE title GLOB '*Ubuntu*'"; done
I did the resize before I switched to Firefox. I haven't needed to search the SQLite DB of my Firefox history recently, but I know an SQLite query will be similar to above. In fact, Firefox stores even more useful data for me, such as a timestamp of all visits to a website, not just the most recent one. To get started, I'd use something like:
sqlite3 -readonly /path/to/firefox/history ".schema"
As always, adjust your paths/globs accordingly when using the above examples. Have fun!
Hi, current-me coming back to update past-me's tutorial for Firefox. I found the following query useful for searching old Firefox histories (adjust paths/globs accordingly for your use case). Enjoy!
sqlite3 places.sqlite "SELECT datetime(visit_date/1000000,'unixepoch'), moz_places.url,title FROM moz_places INNER JOIN moz_historyvisits ON moz_places.id = moz_historyvisits.place_id WHERE title GLOB '*Unix*' ORDER BY visit_date
This query was made with the help of (wikiversity)[https://en.wikiversity.org/wiki/Firefox/Browsing_history_database], Aurelie Herbelot, Mozilla's own wiki, and of course the wonderful SQLite Tutorial.
By "precious", I mean "files that change frequently enough, and whose contents are important enough, that you go out of the way to keep and consult previous versions of them".1 For me, they're:
- Browser SQLite DBs
- Any
.bash_historys - My Downloads Folder (sometimes, I usually treat it as append-only, or only delete installer
.exes) - My email
Maildirs ormboxes.
Can anyone think of others?
Today's post inspired by me minddumping on @xkeeper's forum about having to rescue and prune backups after a drive failure in December.
- I initially wrote "restoring an accidentally-deleted file or undoing changes in a file whose associated program doesn't have an autosave" doesn't count. But while those types of restores are mostly one-offs to me, I shouldn't assume that for others.