Decoding Google Chrome timestamps in SQLite databases

I had occasion to work with Google Chrome histories today.  The timestamp in the History SQLite databases look like unixepoch time at first glance, but they are not. However, the Chrome timestamps are 7 digits longer than unixepoch time.

Unixepoch

Unixepoch time is the number of seconds elapsed since 01/01/1970 00:00:00. It can be converted in a SQLite statement with the datetime() function thusly:

sqlite> SELECT datetime(time, 'unixepoch', 'localtime') AS time FROM table;

"time" is the name of the field containing the (currently) 10-digit date string, e.g., "1307078210", and "table" is the name of the table containing the time field.  The "AS time" displays the column label as "time". Without it, the column takes the full datetime() function as the column name which is a bit ugly and confusing to recipients of the data.

PRTime

The extra seven digits in the Chrome timestamp brought to mind Firefox PRTime. PRTime is the number of microseconds since 01/01/1970 00:00:00. The timestamp needs to be converted to unixepoch format before it can be converted to local time through the SQLite datetime() function. This can be done by dividing the value by one-million. In SQLite, this looks like:

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

Chrome Time (Webkit Format)

If there is an official name for Chrome time, I don't know it.  EDIT: I have learned the name for the Chrome's format is Webkit format).  What I do know, is that Chrome time IS NOT the same as PRTime.  Though it is also a microsecond calculation, its 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. But how do we figure that out?

Two ways, as it turns out.  With SQLite itself, or with the Unix date command.  This is how:

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;

Unfortunately, there are more time formats out there than these.  You may recall I blogged about Apple's Mac Absolute Time in February.  But, armed with this information, You should be able to convert the times formats I've discussed and possibly discover the solution to any others you might encounter.