将Chrome历史记录文件(sqlite)中的datetime字段转换为可读格式 [英] Convert datetime fields in Chrome history file (sqlite) to readable format
问题描述
使用脚本来收集使用时间戳(教育设置)的用户浏览器历史记录。
Firefox 3历史记录保存在sqlite文件中,并且邮票在UNIX时代,通过python中的SQL命令获取并转换为可读格式非常简单:
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还将历史存储在sqlite文件中。但是历史时间戳显然格式化为自1601年1月1日午夜UTC之后的微秒数。
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....
如同Firefox示例(如 2010-01-23 11:22:09),此时间戳如何转换为可读格式?我正在使用python 2.5.x(OS X 10.5中的版本)编写脚本,并导入sqlite3模块....
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....
推荐答案
p>这可能不是世界上最多的Pythonic代码,但这里有一个解决方案:通过这样做来调整时区(EST在这里)欺骗:
This may not be the most Pythonic code in the world, but here's a solution: Cheated by adjusting for time zone (EST here) by doing this:
utctime = datetime.datetime(1601,1,1) + datetime.timedelta(microseconds = ms, hours =-5)
这是功能:它假定Chrome历史记录文件已从另一个帐户复制到/ Users / someuser / Documents / tmp / Chrome / History
Here's the function : It assumes that the Chrome history file has been copied from another account into /Users/someuser/Documents/tmp/Chrome/History
def getcr():
connection = sqlite3.connect('/Users/someuser/Documents/tmp/Chrome/History')
cursor = connection.cursor()
get_time = list(cursor.execute("""SELECT last_visit_time FROM urls"""))
get_url = list(cursor.execute("""SELECT url from urls"""))
stripped_time = []
crf = open ('/Users/someuser/Documents/tmp/cr/cr_hist.txt','w' )
itr = iter(get_time)
itr2 = iter(get_url)
while True:
try:
newdate = str(itr.next())
stripped1 = newdate.strip(' (),L')
ms = int(stripped1)
utctime = datetime.datetime(1601,1,1) + datetime.timedelta(microseconds = ms, hours =-5)
stripped_time.append(str(utctime))
newurl = str(itr2.next())
stripped_url = newurl.strip(' ()')
stripped_time.append(str(stripped_url))
crf.write('\n')
crf.write(str(utctime))
crf.write('\n')
crf.write(str(newurl))
crf.write('\n')
crf.write('\n')
crf.write('********* Next Entry *********')
crf.write('\n')
except StopIteration:
break
crf.close()
shutil.copy('/Users/someuser/Documents/tmp/cr/cr_hist.txt' , '/Users/parent/Documents/Chrome_History_Logs')
os.rename('/Users/someuser/Documents/Chrome_History_Logs/cr_hist.txt','/Users/someuser/Documents/Chrome_History_Logs/%s.txt' % formatdate)
这篇关于将Chrome历史记录文件(sqlite)中的datetime字段转换为可读格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!