William D. Jones

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.

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!

I'm trying to learn how push notifcations work, and, at least on Apple devices, they have a persistent connection to port 5223 to Some Server Apple Owns (APNS). What's the equivalent for Android?

To avoid an XY Problem: Is it possible to set up a "local" server, so that I can get custom notifications-I-would-otherwise-miss delivered from a daemon/BG process on my laptop to my phone (when I'm resting/not at the laptop)?

Sometimes, I get very focused on one task and spend a lot of time on a singular project. Eventually, I'll forget what I was working on before my hyperfocus.

Earlier this year, I created a shell one liner that with grep -v, helps me get a handle on what I've been working on, and helps me decide what I should be working on instead of starting yet another new project :). I figure I'll share it in the hopes it is useful to others:

progress() { find $1 -mtime -$2 -exec stat -c "%y,%n" {} + | sort -t, -r | column -t -s,; }

This function is invoked as such:

progress path/to/projects/tree max_days_to_look_back

Let's go over each part together:

• find $1 -mtime -$2 -exec stat -c "%y,%n" {} +: Find all files under the directory $1 that were last modified less than $2 days ago. Then collect as many of these filenames as possible *e.g. without reaching internal command-line length limits), and run the stat command. Repeat until no more filenames left to collect.

  • stat -c "%y,%n": Print the last modification time in a user friendly format, a comma (,), and then the name of the file.

• sort -t, -r: Use comma as a field separator, and sort in reverse order. In other words, sort the modification times, and then sort by filename if modification times are identical. Reverse sort will sort the filenames from most recently-modified file to oldest.

  The time string from stat won't have any commas, so comma is a reasonable field separator. If you have two files with commas in their filenames with identical modification time (Idk why you would do that, but it's your system, not mine :D), sort order of the filename portion might be unexpected. I'm assuming this isn't a huge limitation :P.

• column -t -s,: column comes from the util-linux project. It has a number of options, but the net effect here is to pretty-print the output of sort/stat into a table. Comma is an input separator for columns; in the output, the columns are separated by whitespace.

(e)grep -v can be used to filter out "noise" via regex. The below example filters out all build-* files/directories (for e.g. out-of-tree Autotools or Cmake builds), .git/ repo directories, and Cargo target/ directories :

$ find ~/Projects -mtime -150 -exec stat -c "%y,%n" {} + | sort -t, -r | column -t -s, > ml.txt
$ wc ml.txt
  141325   565334 19362868 ml.txt
$ grep -v -e build-* -e .git/ -e target/ ml.txt | wc
  13607   54433 1581445

I hope you enjoy! Why yes, I am using this shell script tonight, why do you ask? :)