SOLVED: Translating visit_time in History to Human-Readable Format

This question was asked previously and closed without a solution. I don’t remember where I got the solution, but here it is.

Original Question
Brave Browser stores browser history in a sql lite database. Each visit to a url is recorded in a table called visits. One of the fields in that table is visit_time.

QUESTION: How is visit time converted to human-readable format?

For example, here is the data for the visit made to the Brave Community where I am typing this new topic:

URL: https://community.brave.com/search?q=history%20category%3A48
visit_time: 13250627305551448

In the Brave Browser history, that history record looks like this:

In SQL Lite, the data looks like this:

I forgot where I got a solution to this, using sqllite accessing the history db saved in /Users/dan/Library/Application Support/BraveSoftware/Brave-Browser-Nightly/Default/History (on macOS_), this works

select
datetime(v.visit_time / 1000000 + (strftime(‘%s’, ‘1601-01-01’)), ‘unixepoch’, ‘localtime’)
from visits v
left join urls u on v.url = u.id

That path to the db is for macOS. I don’t know where the db is on other OSs.

The understand the way datetime() translates a number like 13304295185697214 into 2022-08-06 16:33:05, you can look at the documentation for that function at:

https://www.sqlite.org/lang_datefunc.html

1 Like