Convert datetime fields in Chrome history file (sqlite) to readable format

Question

Working on a script to collect users browser history with time stamps ( educational setting). Firefox 3 history is kept in a sqlite file, and stamps are in UNIX epoch time... getting them and converting to readable format via a SQL command in python is pretty straightforward:

sql_select = """ SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch','localtime'), 
                        moz_places.url 
                 FROM moz_places, moz_historyvisits 
                 WHERE moz_places.id = moz_historyvisits.place_id
             """
get_hist = list(cursor.execute (sql_select))

Chrome also stores history in a sqlite file.. but it's history time stamp is apparently formatted as the number of microseconds since midnight UTC of 1 January 1601....

How can this timestamp be converted to a readable format as in the Firefox example (like 2010-01-23 11:22:09)? I am writing the script with python 2.5.x ( the version on OS X 10.5 ), and importing sqlite3 module....

Answer 1

Try this:

sql_select = """ SELECT datetime(last_visit_time/1000000-11644473600,'unixepoch','localtime'),
                        url 
                 FROM urls
                 ORDER BY last_visit_time DESC
             """
get_hist = list(cursor.execute (sql_select))

Or something along those lines

seems to be working for me.

Answer 2

This is a more pythonic and memory-friendly way to do what you described (by the way, thanks for the initial code!):

#!/usr/bin/env python

import os
import datetime
import sqlite3
import opster
from itertools import izip

SQL_TIME = 'SELECT time FROM info'
SQL_URL  = 'SELECT c0url FROM pages_content'

def date_from_webkit(webkit_timestamp):
    epoch_start = datetime.datetime(1601,1,1)
    delta = datetime.timedelta(microseconds=int(webkit_timestamp))
    return epoch_start + delta

@opster.command()
def import_history(*paths):
    for path in paths:
        assert os.path.exists(path)
        c = sqlite3.connect(path)
        times = (row[0] for row in c.execute(SQL_TIME))
        urls  = (row[0] for row in c.execute(SQL_URL))
        for timestamp, url in izip(times, urls):
            date_time = date_from_webkit(timestamp)
            print date_time, url
        c.close()

if __name__=='__main__':
    opster.dispatch()

The script can be used this way:

$ ./chrome-tools.py import-history ~/.config/chromium/Default/History* > history.txt

Of course Opster can be thrown out but seems handy to me :-)

Answer 3

The sqlite module returns datetime objects for datetime fields, which have a format method for printing readable strings called strftime.

You can do something like this once you have the recordset:

for record in get_hist:
  date_string = record[0].strftime("%Y-%m-%d %H:%M:%S")
  url = record[1]