Chromium browsing history database

This guide shows how to read the history database file left by Google Chrome, Chromium, and its derivatives. Like Firefox, Chromium stores browsing history in a sqlite3 format file, but with different property names, and without ".sqlite" or ".sql" extensions. The file is named "History" and located in the Google Chrome profile directory.

The sqlite3 command-line utility is explained in greater detail at Firefox/Browsing history database. To facilitate using the commands, you may navigate to the profile folder directory, copy the database file into your working directory, or put the path to the database file in a variable.

Unlike Firefox, which limits retention of history based on the number of entries, Chromium has a fixed time limit of three months. Before version 37, released in August 2014, an additional file named "Archive History" retained history without time limit, though as can be seen below, it contained fewer tables.

$ sqlite3 History .tables
downloads             meta                  urls                
downloads_url_chains  segment_usage         visit_source        
keyword_search_terms  segments              visits  

$ sqlite3 "Archived History" .tables # removed with version 37, August 2014.
keyword_search_terms  urls                  visits              
meta                  visit_source

If sqlite3 complains that the database is locked, write the filename as a URL (use quotes to protect shell special characters):

$ sqlite3 'file:History?mode=ro&nolock=1' .tables

List URLs with time stamps and titles in terminal:

$ sqlite3 'file:History?mode=ro&nolock=1' "\
    SELECT datetime(last_visit_time/1000000-11644473600,'unixepoch'), 
           url, \
           title \
      FROM urls \
  ORDER BY last_visit_time DESC" |less

Note that this likely won't be a chronological list of sites visited because the urls table doesn't record chronological history. To get that, the visits table needs to be included in the query:

$ sqlite3 'file:History?mode=ro&nolock=1' "\
    SELECT datetime(visits.visit_time/1000000-11644473600,'unixepoch'), \
           urls.url, \
           urls.title \
      FROM urls, visits \
     WHERE urls.id = visits.url \
  ORDER BY visits.visit_time DESC" |less

Generate a CSV file with header and Excel-compatible timestamps, and redirect to a file history.csv:

$ sqlite3 -header -csv 'file:History?mode=ro&nolock=1' "\
    SELECT datetime(visits.visit_time/1000000-11644473600,'unixepoch') as VisitTime, \
           urls.url, \
           urls.title \
      FROM urls, visits \
     WHERE urls.id = visits.url \
  ORDER BY visits.visit_time DESC" > history.csv

This can be turned into a convenience function that takes the input and output filenames as arguments by appending to ~/.bashrc:

crhist() {
  sqlite3 -header -csv "file:$1?mode=ro&nolock=1" "\
    SELECT datetime(visits.visit_time/1000000-11644473600,'unixepoch') as VisitTime, \
           urls.url, \
           urls.title \
      FROM urls, visits \
     WHERE urls.id = visits.url \
  ORDER BY visits.visit_time DESC" > "$2"
}

Some notes about epochs (refence dates) and storage units:

• Chromium timestamps are counted in (integer) microseconds since 1 January 1601 00:00 UTC. This is the same epoch as Windows uses, but each Windows time unit represents 100 nanoseconds.
• Unix timestamps are counted in (integer) seconds since 1 January 1970 00:00 UTC

Conversion to Unix timestamps requires division by 1,000,000 and subtraction of 11,644,473,600 time units. Conversion to Excel timestamps requires further division by 86,400 (seconds in a day) and the addition of 109,572 days (from 1601 to 1901).

Until version 30, released in July 2013, additional files named "History Index" with year and month numbers appended, stored page text snippets to quickly provide suggestions from the address bar based on page text.