Block Porn With Cold Turkey

Ever wondered which websites you visit the most? Here’s a practical way to extract, analyze, and visualize your browsing history using SQL and LibreOffice Calc. This method works for Safari, Firefox, and Chrome-based browsers on macOS and other platforms.

Safari (macOS only)

Step 1: Locate Safari’s History Database

Safari stores your browsing history in a SQLite database located at:

~/Library/Safari/History.db

Make sure Safari is closed before accessing this file to avoid any file lock issues.

Step 2: Open the Database with DB Browser for SQLite

  1. Download and install DB Browser for SQLite.
  2. Open the History.db file.
  3. Navigate to the “Execute SQL” tab.

Step 3: Run the SQL Query

SELECT 
  CASE
    WHEN length(domain_parts) - length(replace(domain_parts, '.', '')) >= 2
    THEN substr(domain_parts, instr(domain_parts, '.') + 1)
    ELSE domain_parts
  END AS base_domain,
  COUNT(*) as total_visits
FROM (
  SELECT 
    substr(url, instr(url, '//') + 2, instr(substr(url, instr(url, '//') + 2), '/') - 1) AS domain_parts
  FROM history_items
  JOIN history_visits ON history_items.id = history_visits.history_item
)
GROUP BY base_domain
ORDER BY total_visits DESC
LIMIT 100;

Firefox

Step 1: Locate the History Database

Firefox stores history in a file called places.sqlite located in your Firefox profile directory:

~/Library/Application Support/Firefox/Profiles/your-profile/places.sqlite (macOS)

~/.mozilla/firefox/your-profile/places.sqlite (Linux)

C:\Users\YourName\AppData\Roaming\Mozilla\Firefox\Profiles\your-profile\places.sqlite (Windows)

Step 2: Open and Run This SQL Query

SELECT 
  CASE
    WHEN length(domain_parts) - length(replace(domain_parts, '.', '')) >= 2
    THEN substr(domain_parts, instr(domain_parts, '.') + 1)
    ELSE domain_parts
  END AS base_domain,
  SUM(visit_count) as total_visits
FROM (
  SELECT 
    url,
    visit_count,
    substr(url, instr(url, '//') + 2, instr(substr(url, instr(url, '//') + 2), '/') - 1) AS domain_parts
  FROM moz_places
)
GROUP BY base_domain
ORDER BY total_visits DESC
LIMIT 100;

Chrome/Chromium-based Browsers (Chrome, Edge, Brave)

Step 1: Locate the History File

The file is named History and located in:

~/Library/Application Support/Google/Chrome/Default/History (macOS)

~/.config/google-chrome/Default/History (Linux)

C:\Users\YourName\AppData\Local\Google\Chrome\User Data\Default\History (Windows)

Step 2: Use This SQL Query

SELECT 
  CASE
    WHEN length(domain_parts) - length(replace(domain_parts, '.', '')) >= 2
    THEN substr(domain_parts, instr(domain_parts, '.') + 1)
    ELSE domain_parts
  END AS base_domain,
  SUM(visit_count) as total_visits
FROM (
  SELECT 
    url,
    visit_count,
    substr(url, instr(url, '//') + 2, instr(substr(url, instr(url, '//') + 2), '/') - 1) AS domain_parts
  FROM urls
)
GROUP BY base_domain
ORDER BY total_visits DESC
LIMIT 100;

Export and Visualize

In each case, export the SQL result to a CSV file using DB Browser. Then open it with LibreOffice Calc (or Excel) to sort, graph, or pivot your most visited websites.

Limitations

  • This method is read-only and local; it does not sync with cloud data (like Chrome sync or iCloud).
  • Subdomain handling is basic. Domains like bbc.co.uk may not always be parsed as intended.
  • Firefox’s visit_count can be unreliable depending on privacy settings.
  • Ensure the browser is closed before opening the history database.

Conclusion

This SQL-based method provides a powerful, local way to analyze your browsing patterns across Safari, Firefox, and Chrome-based browsers. Whether you’re tracking productivity, managing your attention, or just curious, it’s a fast and effective solution.