9

I originally posted this question looking for an answer with using python, got some good help, but have still not been able to find a solution. I have a script running on OS X 10.5 client machines that captures internet browsing history (required as part of my sys admin duties in a US public school). Firefox 3.x stores history in a sqlite db, and I have figured out how to get that info out using python/sqlite3. Firefox 3.x uses a conventional unixtimestamp to mark visits and that is not difficult to convert... Chrome also stores browser history in a sqlite db, but its timestamp is formatted as the number of microseconds since January, 1601. I'd like to figure this out using python, but as far as I know, the sqlite3 module doesn't support that UTC format. Is there another tool out there to convert Chrome timestamps to a human readable format?

SilentGhost
  • 307,395
  • 66
  • 306
  • 293
cit
  • 2,465
  • 5
  • 28
  • 36
  • Very helpful. How did you find out that the Google Chrome timestamps are the number of microseconds since January, 1601? – Richard Neish Sep 30 '14 at 10:34
  • 3
    Chrome SQLite format, including the Jan 1601, is discussed in https://groups.google.com/forum/#!topic/chromium-extensions/c4lnssuNAFI – Juuso Ohtonen Jan 05 '16 at 09:52

2 Answers2

9

Use the datetime module. For example, if the number of microseconds in questions is 10**16:

>>> datetime.datetime(1601, 1, 1) + datetime.timedelta(microseconds=1e16)
datetime.datetime(1917, 11, 21, 17, 46, 40)
>>> _.isoformat()
'1917-11-21T17:46:40'

this tells you it was just past a quarter to 6pm of November 21, 1917. You can format datetime objects in any way you want thanks to their strftime method, of course. If you also need to apply timezones (other than the UTC you start with), look at third-party module pytz.

Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
0

Bash

  $ date -ud @$[13315808702856828/10**6-11644473600] +"%F %T %Z"
    2022-12-18 03:45:02 UTC
  $ printf '%(%FT %T %z)T\n' $[13315808702856828/10**6-11644473600]
    2022-12-17 T19:45:02 -0800

Perl

$ echo ".. 13315808702856828 .." |\
    perl -MPOSIX -pe 's!\b(1\d{16})\b!strftime(q/%F/,gmtime($1/1e6-11644473600))!e'
.. 2022-12-17 ..
mosh
  • 323
  • 2
  • 8