What format is the Safari History.db history_visits.visit_time in?

It's the number in seconds since 00:00:00 UTC on 1 January 2001. It must be coming from an NSDate.

NSDate objects encapsulate a single point in time, independent of any particular calendrical system or time zone. Date objects are immutable, representing an invariant time interval relative to an absolute reference date (00:00:00 UTC on 1 January 2001).

— NSDate Class Reference

To get a decent human value out of it, you must add 978307200 (the epoch for 2001-01-01 00:00:00).

This query should give you what you want:

.headers onselect datetime(v.visit_time + 978307200, 'unixepoch', 'localtime') as date, v.visit_time + 978307200 as epoch, v.visit_time, i.domain_expansion, i.urlfrom history_items i left join history_visits v on i.id = v.history_itemorder by i.id desclimit 100;

Example output:

date|epoch|visit_time|domain_expansion|url2015-12-31 11:51:27|1451562687.28465|473255487.284646|duckduckgo|https://duckduckgo.com/?q=current+timestamp+2015-12-31+11:51&t=osx

PS: Just for future reference, the Safari db file is located at ~/Library/Safari/History.db

To convert the visit_time value in the history.db in an excel spread sheet, open the history.db file in a tool such as DB browser for SQLLite (Windows) and export the history_visits values to a CSV file.

Open the CSV file and create a column where you will populate your values in human readable time adjusted to your time zone, and use the following formula convert your NSDate:

=((((C2+978307200)/60)/60)/24)+DATE(1970,1,1)+(-5/24)

In the above formula, the time value is in cell C2, and my time zone GMT-5. To adjust to your own time zone adjust the statement in the last set of parenthesis. Presently I have (-5/24) to represent GMT-5.

When I first approached this conversion, I mistakenly assumed the time in the history.db to be epoch time, which starts at 1/1/1970, and did not understand why there was such a skew in time. Adding the required conversion factor +978307200 solved the problem.

I found the domain_expansion field to be null in some cases, here's a modified query:

SELECT SUBSTR(SUBSTR(url, INSTR(url, '/')+2),1,INSTR(SUBSTR(url, INSTR(url, '/')+2),'/') - 1) domain,url,datetime(hv.visit_time + 978307200, 'unixepoch', 'localtime') visit_time FROM history_items hiJOIN history_visits hv on hi.id = hv.history_item;