Search google chrome history by date

chrome_history_by_date.py

# Your database is probably in: ~/Library/Application Support/Google/Chrome/Default/History
# Copy it to ./chrome_history

import sqlite3

conn = sqlite3.connect("./chrome_history")

# Manually exploring the db and foreign keys etc

# print(conn.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall())
"""
  meta
  downloads
  downloads_url_chains
  downloads_slices
  typed_url_sync_metadata
  urls
  sqlite_sequence
  visits
  visit_source
  keyword_search_terms
  segments
  segment_usage
"""

# print(conn.execute("pragma table_info('visits')").fetchall())
"""
  0, 'id', 'INTEGER', 0, None, 1
  1, 'url', 'INTEGER', 1, None, 0
  2, 'visit_time', 'INTEGER', 1, None, 0
  3, 'from_visit', 'INTEGER', 0, None, 0
  4, 'transition', 'INTEGER', 1, '0', 0
  5, 'segment_id', 'INTEGER', 0, None, 0
  6, 'visit_duration', 'INTEGER', 1, '0', 0
  7, 'incremented_omnibox_typed_score', 'BOOLEAN', 1, 'FALSE', 0
"""

# Show all foreign key relationships
# print(conn.execute("""
# SELECT sql
#   FROM (
#         SELECT sql sql, type type, tbl_name tbl_name, name name
#           FROM sqlite_master
#           UNION ALL
#         SELECT sql, type, tbl_name, name
#           FROM sqlite_temp_master
#        )
#   WHERE
#     type != 'meta'
#     AND sql NOTNULL
#   ORDER BY substr(type, 2, 1), name
# """).fetchall())
"""
  CREATE TABLE downloads (id INTEGER PRIMARY KEY,current_path LONGVARCHAR NOT NULL,target_path LONGVARCHAR NOT NULL,start_time INTEGER NOT NULL,received_bytes INTEGER NOT NULL,total_bytes INTEGER NOT NULL,state INTEGER NOT NULL,danger_type INTEGER NOT NULL,interrupt_reason INTEGER NOT NULL,end_time INTEGER NOT NULL,opened INTEGER NOT NULL,referrer VARCHAR NOT NULL,by_ext_id VARCHAR NOT NULL,by_ext_name VARCHAR NOT NULL,etag VARCHAR NOT NULL,last_modified VARCHAR NOT NULL,mime_type VARCHAR(255) NOT NULL,original_mime_type VARCHAR(255) NOT NULL, guid VARCHAR NOT NULL DEFAULT '', hash BLOB NOT NULL DEFAULT X'', http_method VARCHAR NOT NULL DEFAULT '', tab_url VARCHAR NOT NULL DEFAULT '', tab_referrer_url VARCHAR NOT NULL DEFAULT '', site_url VARCHAR NOT NULL DEFAULT '', last_access_time INTEGER NOT NULL DEFAULT 0, transient INTEGER NOT NULL DEFAULT 0)",
  CREATE TABLE downloads_slices (download_id INTEGER NOT NULL,offset INTEGER NOT NULL,received_bytes INTEGER NOT NULL, finished INTEGER NOT NULL DEFAULT 0,PRIMARY KEY (download_id, offset) )',
  CREATE TABLE downloads_url_chains (id INTEGER NOT NULL,chain_index INTEGER NOT NULL,url LONGVARCHAR NOT NULL, PRIMARY KEY (id, chain_index) )',
  CREATE TABLE keyword_search_terms (keyword_id INTEGER NOT NULL,url_id INTEGER NOT NULL,lower_term LONGVARCHAR NOT NULL,term LONGVARCHAR NOT NULL)',
  CREATE TABLE meta(key LONGVARCHAR NOT NULL UNIQUE PRIMARY KEY, value LONGVARCHAR)',
  CREATE TABLE segment_usage (id INTEGER PRIMARY KEY,segment_id INTEGER NOT NULL,time_slot INTEGER NOT NULL,visit_count INTEGER DEFAULT 0 NOT NULL)',
  CREATE TABLE segments (id INTEGER PRIMARY KEY,name VARCHAR,url_id INTEGER NON NULL)',
  CREATE TABLE sqlite_sequence(name,seq)',
  CREATE TABLE typed_url_sync_metadata (storage_key INTEGER PRIMARY KEY NOT NULL,value BLOB)',
  CREATE TABLE "urls"(id INTEGER PRIMARY KEY AUTOINCREMENT,url LONGVARCHAR,title LONGVARCHAR,visit_count INTEGER DEFAULT 0 NOT NULL,typed_count INTEGER DEFAULT 0 NOT NULL,last_visit_time INTEGER NOT NULL,hidden INTEGER DEFAULT 0 NOT NULL)',
  CREATE TABLE visit_source(id INTEGER PRIMARY KEY,source INTEGER NOT NULL)',
  CREATE TABLE visits(id INTEGER PRIMARY KEY,url INTEGER NOT NULL,visit_time INTEGER NOT NULL,from_visit INTEGER,transition INTEGER DEFAULT 0 NOT NULL,segment_id INTEGER,visit_duration INTEGER DEFAULT 0 NOT NULL, incremented_omnibox_typed_score BOOLEAN DEFAULT FALSE NOT NULL)',
  CREATE INDEX keyword_search_terms_index1 ON keyword_search_terms (keyword_id, lower_term)',
  CREATE INDEX keyword_search_terms_index2 ON keyword_search_terms (url_id)',
  CREATE INDEX keyword_search_terms_index3 ON keyword_search_terms (term)',
  CREATE INDEX segment_usage_time_slot_segment_id ON segment_usage(time_slot, segment_id)',
  CREATE INDEX segments_name ON segments(name)',
  CREATE INDEX segments_url_id ON segments(url_id)',
  CREATE INDEX segments_usage_seg_id ON segment_usage(segment_id)',
  CREATE INDEX urls_url_index ON urls (url)',
  CREATE INDEX visits_from_index ON visits (from_visit)',
  CREATE INDEX visits_time_index ON visits (visit_time)',
  CREATE INDEX visits_url_index ON visits (url)',)]
"""

# Understanding the timestamp
#
# last_hit = conn.execute("SELECT * FROM visits ORDER BY visit_time DESC LIMIT 1 OFFSET 1;").fetchall()[0]
# print(last_hit)
# url_id = last_hit[1]
# url = conn.execute("SELECT * FROM urls WHERE id = {};".format(url_id)).fetchone()
# print(url)
#
# visit_time 13211055964784279 => 2019-08-23 18:46:04 => 1566582364000
# visit_time 13211055963583598 => 2019-08-23 18:46:03 => 1566582363000
# transform_gtime_to_time = ts => (Math.floor(ts / 1000000) - 11644473600) * 1000
# transform_time_to_gtime = ts => ((ts / 1000) + 11644473600) * 1000000
# So if we want to find 9th June 2019 that's
# transform_time_to_gtime(new Date("2019-06-09 23:59").getTime())
# 13204594740000000
# transform_time_to_gtime(new Date("2019-06-09 00:01").getTime())
# 13204508460000000
# transform_time_to_gtime(new Date("2019-05-30 12:39").getTime())
# 13203689940000000
# transform_time_to_gtime(new Date("2019-05-30 10:38").getTime())
# 13203682680000000

# results = conn.execute("SELECT * FROM visits DESC WHERE visit_time < 13204594740000000 AND visit_time > 13204508460000000 ORDER BY visit_time DESC;").fetchall()
results = conn.execute("SELECT * FROM visits DESC WHERE visit_time < 13203689940000000 AND visit_time > 13203609940000000 ORDER BY visit_time DESC;").fetchall()

for res in results:
  url = conn.execute("SELECT * FROM urls WHERE id = {};".format(res[1])).fetchone()
  print(url[1])