主要的性能问题在于你从SQLite中提取数据,加载到Python中,在Python中进行转换,然后
逐个日期将其放回数据库。这永远不会高效。
相反,
使用SQLite自己内置的日期和时间函数。看起来
atime
是Unix纪元时间。
update hash set atime = datetime(atime, 'unixepoch', 'localtime');
但是你
可能不想将日期存储在本地时区。时区变得复杂,还有夏令时,其中有缺失和重叠的时间...这只会导致麻烦。如果没有指定该时区,绝对不要将日期时间存储在本地时区中!除非你有一个非常好的理由,否则请将其存储为UTC。
update hash set atime = datetime(atime, 'unixepoch');
通常情况下,如果你想做SQLite不支持的事情,
创建一个用户自定义函数并在查询中使用它。这将比使用内置的SQLite函数效率低一些,但比选择、转换和更新更高效。
它看起来应该是这样的。
def epoch_to_iso8601(epoch):
return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime((float(epoch))))
con.create_function("epoch_to_iso8601", 1, epoch_to_iso8601)
然后你可以在查询中使用epoch_to_iso8601
。
update hash set atime = epoch_to_iso8601(atime);
请注意,这与存储过程不同。由于没有SQLite服务器,所有代码都在您的进程中运行,因此此函数是每个进程的函数。
请参见
sqlite3.create_function。
真正的问题在于你将日期时间存储为字符串。这使得它们变得缓慢和难以处理。这意味着你必须选择一个单一的格式。这意味着你必须解析该格式才能对其进行任何操作。这意味着你不能使用内置的SQLite日期和时间函数(尽管它们很稀少)。
实际上,你想要做的是保留 "atime" 作为Unix纪元时间,并根据查询的需要进行格式化。
select datetime(atime, 'unixepoch') from hash;
幸运的是,SQLite在类型方面非常宽松,并且会将文本字段atime
转换为数字,尽管这会带来性能和存储开销。
理想情况下,您希望将
atime
更改为使用
datetime
类型,但在SQLite中这很困难。它不支持删除或修改现有列。相反,您需要转储表中的数据,重新创建表并导入数据。仅有30,000条记录,这应该非常快。
切换到CSV模式,将输出发送到文件,并选择全部内容。
sqlite> .mode csv hash
sqlite> .output hash.out
sqlite> select * from hash;
删除现有表并重新创建相同的表,但将atime
作为datetime
。
sqlite> drop table hash;
sqlite> create table hash ( atime datetime, and the other columns );
导入转储。
sqlite> .import hash.out hash
Atime
是什么类型?看起来它是一个文本字段,这是个问题。 - Schwern