What format is Safari History.db history_visits.visit_time?

Question

When viewing History.db from Safari, there is a table with a name history_visitsthat has a column with a name visit_timethat is a value REAL. It has such meanings as 470799793.096987. What format is this? I would like to see this in a format such as 12/08/2015 05:12:05.

Answer 1

This number is in seconds from 00:00:00 UTC on January 1, 2001. It must come from NSDate.

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

- Link to the NSDate class

To get a decent human value, you must add 978307200(era 2001-01-01 00:00:00).

This request should provide you with 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;

Output Example:

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: For future reference, the Safari db file is in ~/Library/Safari/History.db

Answer 2

visit_time history.db excel, history.db , SQLLite (Windows), history_visits CSV.

CSV , , , : NSDate:

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

C2 GMT-5. , . (-5/24) GMT-5.

, , .db , 1/1/1970, , . [phone] .