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

Question

When looking at the History.db from Safari, there's a table named history_visits which has a column named visit_time, which is a REAL value. It has values such as 470799793.096987. What format is that in? I'd like to see it in a format such as 12/08/2015 05:12:05.

Answer 1

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 on

select datetime(v.visit_time + 978307200, 'unixepoch', 'localtime') as date, v.visit_time + 978307200 as epoch, v.visit_time, i.domain_expansion, i.url
from history_items i left join history_visits v on i.id = v.history_item
order by i.id desc
limit 100;

Example output:

date|epoch|visit_time|domain_expansion|url
2015-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