SQL to query convert chrome time stamp

Question

Level 1

8 points

SQL to query convert chrome time stamp

I am using DB Browser for SQLite and I'm trying to query the Chrome history database and return the chromium timestamp to be human readable and include milliseconds, alternative options I've tried return the incorrect year, can anyone point me in the right direction please.

For example I would like acheive the following output for the time stamp 14/06/2022 10:05:30.25

SELECT u.url AS URL, u.title AS Title, u.visit_count As "Visit Count", datetime(u.last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch', 'localtime') As "Last Visited Date Time", datetime(v.visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch', 'localtime') AS "Visited Date Time" FROM urls u, visits v WHERE u.id = v.url

Posted on Jun 14, 2022 3:41 AM

Reply

Answer 1

Top-ranking reply

etresoft

Level 9

58,090 points

Jun 14, 2022 11:11 AM in response to Sanchez4006

Try this:

SELECT 
  u.url AS URL, 
  u.title AS Title, 
  u.visit_count As "Visit Count", 
  strftime('%Y-%m-%d %H:%M:%f', (u.last_visit_time/1000000.0) - 11644473600, 'unixepoch', 'localtime') As "Last Visited Date Time", 
  strftime('%Y-%m-%d %H:%M:%f', (v.visit_time/1000000.0) - 11644473600, 'unixepoch', 'localtime') AS "Visited Date Time" 
FROM urls u, visits v 
WHERE u.id = v.url;

Reply

Answer 2

VikingOSX

Level 10

126,516 points

Jun 14, 2022 5:59 AM in response to Sanchez4006

I don't use Chome, but here is a SQLite script to generate a CSV from Safari history:

sql_dtu.zsh > ~/Desktop/safari.csv

#!/bin/zsh

: <<'COMMENT'
Generate a CSV document with headers showing current Safari History
database content. You can view the columnar CSV in a scrollable QuickLook window,
or load it into a spreadsheet

Usage in Terminal:
./sql_dtu.zsh > ~/Desktop/hist.csv

VikingOSX, 2020-07-16, Apple Support Communities, No warranties expressed or implied.
COMMENT

sqlite3 -header -csv $HOME/Library/Safari/History.db \
"SELECT DISTINCT
    datetime(visit_time + 978307200, 'unixepoch', 'localtime') AS visit_time,
    coalesce(nullif(title,''), 'Unavailable') as title, url
FROM
    history_visits
INNER JOIN
    history_items ON
        history_items.id = history_visits.history_item;"
exit 0

Where data is missing in the history data title field, I test for that and plug-in "Unavailable".

Reply