50

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?

naXa stands with Ukraine
  • 35,493
  • 19
  • 190
  • 259
Melab
  • 2,594
  • 7
  • 30
  • 51

6 Answers6

70

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
Richard Neish
  • 8,414
  • 4
  • 39
  • 69
  • 2
    Query adjusted to handle timezone: `datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01T05:30:00')), 'unixepoch')` where `T05:30:00` is my timezone. Substitute it with yours. – matrix Jan 20 '18 at 22:53
  • 2
    SQLite knows your timezone. You just have to ask for it: `datetime(visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch', 'localtime')` – Deven T. Corzine May 22 '20 at 19:38
15

visits.visit_time is in microseconds since January 1, 1601 UTC which is similar but not to be mistaken for Windows filetime which is the number of 100 nanoseconds since January 1, 1601 UTC.

Trivia: Why 1601?
I think the popular answer is because the Gregorian calendar operates on a 400-year cycle, and 1601 is the first year of the cycle that was active at the time Windows NT was being designed. In other words, it was chosen to make the math come out nicely. January 1, 1601 is origin of COBOL integer dates. It is also day 1 by ANSI date format. And if you speculate further according to ISO8601 which is the format in which it is in, ISO8601 works as far back as the year 1581. Prior to 1583 time was based on the proleptic Gregorian calendar which has 366 days per year. Perhaps they just rounded up to the next century.

downloads.start_time is the number of seconds since January 1, 1970 UTC

Trivia: Why 1970?
Well, I'm glad you asked.. It didn't used to be.. Originally it was January 1, 1971 but was later rounded to January 1, 1970. January 1, 1970 is considered to be the birth of UNIX.

It's worth noting that Firefox formats time as the number of microseconds since January 1, 1970 and the name for the format is PRTime

All of these are in an ISO 8601 EPOCH format.

davidcondrey
  • 34,416
  • 17
  • 114
  • 136
  • 2
    Source for "originally it was January 1, 1971"? – Pacerier Oct 09 '16 at 11:30
  • 5
    [Unix Programmer's Manual 1st Edition defines the Unix time as "the time since 00:00:00, Jan. 1, 1971, measured in sixtieths of a second".](http://stackoverflow.com/q/1090869/1922144) – davidcondrey Oct 10 '16 at 13:39
9

Chromes Timestap is not Unixepoch!!

Chrome's base time is 01/01/1601 00:00:00. To calculate local time, Chrome time has to be converted to seconds by dividing by one-million, and then the seconds differential between 01/01/1601 00:00:00 and 01/01/1970 00:00:00 must be subtracted. There are two ways you can do this, viz SQLite itself and Unix.

SQLITE:

sqlite> SELECT strftime('%s', '1601-01-01 00:00:00');
-11644473600

DATE:

$ date +%s -d 'Jan 1 00:00:00 UTC 1601'
-11644473600

In both commands above, the "%s" represents unixepoch time. The commands calculate the number of seconds between unixepoch time (1970) and the subsequent date (Chrome time base, 1601). Note that the seconds are negative. Of course, this is because you have to count backwards from 1970 to 1601! With this information, we can convert Chrome time in SQLite like this:

sqlite> SELECT datetime((time/1000000)-11644473600, 'unixepoch', 'localtime') AS time FROM table;

Have a good read here.

Anandu M Das
  • 219
  • 2
  • 11
4

Here is a compact expression to convert WebKit Time:

sqlite> SELECT datetime(time/1e6-11644473600,'unixepoch','localtime') AS time FROM table;
grossdm
  • 65
  • 5
3

I'm new to coding so I'm not sure how you do it with sql, however I can show you a method in c#. I am hoping this would help someone.

If the time value given in the database is :
13029358986442901. Select only the first 11 digits 13029358986. You can convert this to time using : 

DateTime dateTimeVar = new DateTime(1601,1,1).AddSeconds(time);

The answer here was : 19-11-2013 18:23:06 And this was without your time zone conversion.

Jack
  • 10,943
  • 13
  • 50
  • 65
Silver
  • 39
  • 4
  • 1
    Instead of truncating all digits after the 11th one you could also divide the time value by 1,000,000 which still gives you a fairly accurate date – Bryida Mar 22 '17 at 13:44
2

You can substract 11644473600000 (1/1/1601 is -11644473600000 in unixepoch) and treat it as a regulat unix epoch timestamp this is assuming miliseconds.

milis: 11644473600000 seconds: 11644473600

Mijacr
  • 161
  • 1
  • 4