What is the format of Chrome's timestamps?

google-chrome, sqlite, timestamp

Solution

The answer is given in this question: "[Google Chrome's] timestamp is formatted as the number of microseconds since January, 1601"

So for example in my sample history database, the query

SELECT
  datetime(visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch', 'localtime')
FROM visits
ORDER BY visit_time DESC
LIMIT 10;

gives the results:

2014-09-29 14:22:59
2014-09-29 14:21:57
2014-09-29 14:21:53
2014-09-29 14:21:50
2014-09-29 14:21:32
2014-09-29 14:21:31
2014-09-29 14:16:32
2014-09-29 14:16:29
2014-09-29 14:15:05
2014-09-29 14:15:05

Using your timestamp value of 13029358986442901:

SELECT
  datetime(13029358986442901 / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch', 'localtime')

the result is:

2013-11-19 18:23:06

Problem

I'm using SQLite Database Browser to read information from a database containing the browsing history for Google Chrome. My current code that I am executing in the "Execute SQL" panel looks like this: ``` SELECT last_visit_time,url,title FROM urls WHERE url LIKE {PLACEHOLDER} AND title LIKE {PLACEHOLDER} ``` The stuff on the "WHERE" line is blocked out with `{PLACEHOLDER}` for privacy purposes. Now, I want to make it such that the data returned in the `last_visit_time` column is readable instead of a jumbled mess like `13029358986442901`. How do I do this and how do I convert Chrome's timestamp to a readable format? How do I get it to order them (the returned rows) by `last_visit_time`?

Original source

Related problems