48

I'm trying to figure out how to determine the time websites were visited in the Safari history. (I can't imagine why this isn't easy to do like in every other browser I've ever used).

NOTE: I'm running Safari 9.0.3 and El Capitan 10.11.3.

I've done some research and found some files in ~/Library/Safari. The file History.db file looks possibly promising but I haven't been able to figure out how to find the timestamp from this yet.

nohillside
  • 100,768
Darren
  • 581

6 Answers6

49

So I woke up today to my sleep-tracker app telling me I went to bed an hour earlier than I thought I did, so naturally I assumed the app was the one who made the stupid mistake. Luckily I'd surfed the web before going to bed, but apparently Apple changed things up again as of High Sierra or Safari 11 so the other answers didn't work for me.

I found out how to get the visit times, and since I went way to far to get to the bottom of this highly unimportant issue already, I thought I'd share my findings here:

Edit

Okay so I got carried away a little more and wrote a bash one-liner to do all the hard work for you. Just open the terminal and copy-past it in. It should work without having to install anything, unless apple has changed something again in the mean time.

You can ignore the empty lines in the output (they are http-redirects) and you can change the last number 30 in the command to filter the number of lines you want to see.

sqlite3 ~/Library/Safari/History.db 'select visit_time,title from history_visits order by visit_time desc;' \
 | while read i; do d="${i%%.*}"; echo "$(date -r $((d+978307200))) | ${i#*|}"; done \
 | head -n 30

Original answer

So instead of having a simple folder with all the history like before, they now write the history into a database. Here's how you get the info if you really want to:

  1. The data is now stored in an SQLite Database, so you're gonna need some tool to read that. I suggest the DB Browser for SQLite. Just install it the usual way.
  2. Now open the database file. It is located here: /Users/[yourname]/Library/Safari/History.db.
  3. In the Browse Data Tab click the Table: dropdown and select history_visits. History_visits screenshot
  4. Now, sort by visit_time and find the thing you are interested in.
  5. Those numbers under the visit_time column are a Core Data Timestamp, so to get something human-readable you need a converter. Simply use this one: https://www.epochconverter.com/coredata

Hope this solves your history-related conundrum.

slm
  • 4,798
Leon S.
  • 591
  • 1
    You are awesome mate!! Works like a charm in Mojave too!! – Jeeva Dec 20 '18 at 06:02
  • 2
    The other solutions werent working on mojave but this works great!! Thank you so much mate :) – Flaming_Dorito Feb 24 '19 at 23:12
  • 1
    If it fails just copy the database file to another place, or maybe closing safari is sufficient – Simon Mar 27 '19 at 22:54
  • cool, but I cannot get it to open manually or on terminal using the command. what's wrong? – dia Dec 07 '20 at 10:41
  • If you add the text > ~/SafariHist.txt to the end of this command, it will output directly to a file in your home directory. Useful for exporting / viewing thousands of entries. Great tip, your work was very helpful! – Demis Jun 29 '21 at 20:33
  • 1
    The Core Data timestamp can be converted to a human readable form thusly: select datetime(visit_time + 978307200, 'unixepoch', 'localtime') as 'Visit Time', …. The constant 978307200 is the difference in seconds between the Unix epoch (1970-01-01) and Apple's epoch (2001-01-01). – Dave Land Aug 10 '21 at 04:38
  • 1
    I cannot open it from DB Browser for SQLite, which gives Could not open database file. Reason: unable to open database file. – alper Jul 21 '22 at 10:33
  • Copy the file to your desktop or wherever, it should open then. I suspect there is a lock on it when Safari is running. – Ger May 05 '23 at 07:57
  • I could double-finger-tap on the touchpad over visit_time and choose "Edit display format", then choose "Apple NSDate to date" to have the date in legible format. Maybe the hours could be a bit off for time zones etc but it's still pretty close? – Jonny May 24 '23 at 10:36
  • 1
    mate, you're so awesome for sharing this. just a side note: because of permissions I couldn't handle the file from original location, so I copied it to the desktop and changed the path. – Line Oct 06 '23 at 16:29
  • Error: unable to open database "/Users/.../Library/Safari/History.db": authorization denied – Mohammed Shareef C Oct 15 '23 at 07:02
22

I modified the oneliner that meonlol gave above. This way it is just pure SQL query. This also includes URL and exports to CSV.

sqlite3 ~/Library/Safari/History.db \
    'SELECT
        datetime(V.visit_time+978307200, "unixepoch", "localtime") AS datetime,
        I.url,
        V.title
 FROM history_visits V
 LEFT JOIN history_items I on V.history_item = I.id
 ORDER BY visit_time DESC
 LIMIT 5000;' -header -csv > safari-history.csv

In Mojave, and possibly later, accessing the file in terminal seems limited. Either grant full access to Terminal in System Preferences or use Finder to open ~/Library/Safari and then copy History.db to somewhere else.

nohillside
  • 100,768
priiiiit
  • 321
  • 3
    +1 on copying the database to a location outside of the Library tree (at least in Mojave). Seems a required step to access the database. – DaveL17 May 20 '19 at 11:53
  • 2
    You still can access the file on Mojave if you grant Terminal full access in System Preferences. – nohillside May 26 '19 at 12:23
  • Add -header -csv > safari_history.csv onto the very end of this and it'll save the result in CSV format – John Sandall Mar 03 '21 at 00:36
  • Forgive the formatting. Get URLs for a given day: sqlite3 ~/path/to/History.db 'select url from (select date(V.visit_time+978307200, "unixepoch", "localtime") AS date, I.url as url, V.title from history_visits V left join history_items I on V.history_item = I.id where date = "2021-06-25");' -header -csv > ~/safari-history.csv – Joe Jun 26 '21 at 17:33
  • After editing the list, you can do (on macOS): ```input=~/path/to/safari-history.csv while read line; do open $line; done < "$input"
    – Joe Jun 26 '21 at 18:02
  • This gives Error: in prepare, unrecognized token: "\" (1) in terminal – Jonny May 24 '23 at 10:32
18

I just tried this in 10.11.4 and it works; you can see the history. If someone clears the cache, this goes away.

Go to the following location:

/Users/yourname/Library/Caches/Metadata/Safari/History/

From here you can change the view to List View and sort by Date Modified.

fsb
  • 22,799
  • 4
    still it would be nice if Apple work some on this - simple extension would be helpful like this for chrome https://chrome.google.com/webstore/detail/better-history/obciceimmggglbmelaidpjlmodcebijb – Bruno Jul 20 '16 at 18:27
  • here is a new link for Better History extension : https://chrome.google.com/webstore/detail/better-history/egehpkpgpgooebopjihjmnpejnjafefi?hl=en – tama Mar 03 '21 at 07:51
  • 2
    In latest version (12.2.1), the location cannot be found. It should be revised to: /Users/yourname/Library/Safari/LocalStorage – robothead Feb 23 '22 at 06:42
  • 1
    This folder does not exist on my end is it normal? – alper Jul 21 '22 at 10:17
  • Apple is nowadays a pain in the a**, in many simple use cases. we have to go very extreme for very simple use cases. And when an update comes, your bluetooth devices or something will stop working. I'm afraid of updates now. I often think about the good old windows and ubuntu days – Mohammed Shareef C Oct 15 '23 at 07:08
  • I came here for Safari on iOS. Gives you something stupid like "Tuesday afternoon". Unfortunately the workarounds here are no use for iOS. In case someone has the same problem, there's an utility called iMazing (https://imazing.com/) that can extract the history from an iOS backup. It's the swiss pocket knife for iOS devices. Bought that a long time ago, saved me again here. I think they have do an evaluation period as well. – Holger Böhnke Jan 10 '24 at 21:39
2

I wanted the URL as well as window title, plus the ability to search or select etc which can be added in the query (illustrating mentionof google anywhere). In this instance I have remove any long tail of URL after ampersand (&) char. The tac puts it back in latest at the end order. In terminal session:

sqlite3 ~/Library/Safari/History.db 'SELECT datetime(history_visits.visit_time+978307200, "unixepoch", "localtime"), history_visits.title || " @ " || substr(history_items.URL,1,max(length(history_items.URL)*(instr(history_items.URL,"&")=0),instr(history_items.URL,"&"))) as Info FROM history_visits INNER JOIN history_items ON history_items.id = history_visits.history_item where Info like "%google%" ORDER BY visit_time DESC  LIMIT 30;'|tac

Reviewing browsing at a given past date involves time & date complexities but this may help, from a given date and time, edit2020-01-19 15:30 for your need...

sqlite3 ~/Library/Safari/History.db 'SELECT datetime(history_visits.visit_time+978307200, "unixepoch", "localtime"), history_visits.title || " @ " || substr(history_items.URL,1,max(length(history_items.URL)*(instr(history_items.URL,"&")=0),instr(history_items.URL,"&"))) as Info FROM history_visits INNER JOIN history_items ON history_items.id = history_visits.history_item where history_visits.visit_time>(julianday("2020-01-19 15:30")*86400-211845068000)  ORDER BY visit_time ASC LIMIT 30;'

Giving output like:

2020-01-20 16:35:15|Amazon.co.uk: carborundum wheel @ https://www.amazon.co.uk/s/ref=nb_sb_noss_1?url=search-alias%3Daps&
2020-01-20 16:35:15|Amazon.co.uk: carborundum wheel @ https://www.amazon.co.uk/s?k=carborundum+wheel&
2020-01-20 16:35:41|Multi-Sharp 1301 Rotary Mower/Garden Tool Sharpener: Amazon.co.uk: Garden & Outdoors @ https://www.amazon.co.uk/Multi-Sharp-Rotary-Mower-Garden-Sharpener/dp/B0001OZH6M/ref=sr_1_11?keywords=carborundum+wheel&
2020-01-20 16:37:14|Amazon.co.uk: grinding wheel @ https://www.amazon.co.uk/s?k=grinding+wheel&
2020-01-20 16:39:26|Amazon.co.uk: grinding wheel @ https://www.amazon.co.uk/s?k=grinding+wheel&
2020-01-20 16:39:26|Amazon.co.uk: grinding wheel @ https://www.amazon.co.uk/s?k=grinding+wheel&
2020-01-20 16:40:15|FERM BGA1057 Grind Stone: Amazon.co.uk: DIY & Tools @ https://www.amazon.co.uk/Ferm-BGA1057-FERM-Grind-Stone/dp/B00AW9GVO8/ref=sr_1_71?keywords=grinding+wheel&

One last note, if you open a sqlite3 session to do lots of queries I found that the database does not seem to update if you continue to use Safari. Maybe a snapshot copy is used? (This on El Capitan. If you have to make a file copy on later OS versions obviously no updates show!)

colin
  • 79
1

This path has changed in Sierra

In 10.12.6 go to

/Users/yourname/Library/Caches/Metadata/Safari/History

user258682
  • 11
0

In Sierra now just use Finder to search for a folder called Safari, then go into the History folder, and you can change the view as mentioned before.

Amanda
  • 1