在sqlite3列中存储Python datetime.time的最佳方法是什么？

Question

我试图使用Python + SQLite3替换我的SAS使用，将我的数据从SAS数据集移动到SQLite数据库。我有许多时间字段，在Python中以datetime.time对象的形式表示。由于SQLite是“轻型类型”，我正在寻求关于在列中存储时间的建议。 （我知道我必须编写Python适配器等来读取和写入对象到列中）需要考虑以下功能：

• SQLite处理查询时对该列的能力。（例如，我能否选择出发生在两个时间之间的行？）
• 字段大小。（我的表通常有数亿行。）
• 人类可读性。（我考虑将时间作为整数存储：自午夜以来的微秒数。但这使得查看数据更加困难。）

有没有人已经解决了这个问题并感到满意呢？

- jdmarino

Answer 1

有一个通用的方法可以将任何可序列化的Python对象存储在sqlite表中。

• 使用sqlite3.register_adapter注册函数进行转换，将Python对象转换为int、long、float、str(UTF-8编码)、unicode或buffer。
• 使用sqlite3.register_converter注册函数来将文本转换为Python对象。输入始终为文本，因为内部，sqlite将所有内容都存储为文本格式。

下面是将datetime.time对象存储在sqlite表中的代码示例：

import sqlite3
import datetime as DT

def adapt_timeobj(timeobj):
    return ((3600*timeobj.hour + 60*timeobj.minute + timeobj.second)*10**6 
            + timeobj.microsecond)

def convert_timeobj(val):
    val = int(val)
    hour, val = divmod(val, 3600*10**6)
    minute, val = divmod(val, 60*10**6)
    second, val = divmod(val, 10**6)
    microsecond = int(val)
    return DT.time(hour, minute, second, microsecond)


# Converts DT.time to TEXT when inserting
sqlite3.register_adapter(DT.time, adapt_timeobj)

# Converts TEXT to DT.time when selecting
sqlite3.register_converter("timeobj", convert_timeobj)

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()

# declare timecol to be of type timeobj
cur.execute("create table test (timecol timeobj)")

cur.executemany("insert into test (timecol) values (?)", 
                [(DT.time(1,2,3,4), ), (DT.time(5,6,7,8),) ])

您可以在SQL中使用不等式，但请注意被比较的值是由adapt_timeobj返回的，而不是datetime.time对象。如果adapt_timeobj函数返回的整数与相应的datetime.time对象以相同的顺序可排序（就像上面的示例一样），则SQL中的不等式将按预期工作。

cur.execute("select timecol from test where timecol < ?",
            [DT.time(4,5,6)])
print(cur.fetchall())
# [(datetime.time(1, 2, 3, 4),)]

cur.execute("select timecol from test where timecol < ?",
            [DT.time(8,0,0)])
print(cur.fetchall())
# [(datetime.time(1, 2, 3, 4),), (datetime.time(5, 6, 7, 8),)]

con.commit()
cur.close()
con.close()

注意：如果您查看编辑历史记录，您会看到一个更简单的替代方案，用int而不是str存储数据，用于adapt_timeobj和convert_timeobj。这种方法更简单，但将数据存储为int更快且更节省内存。

- unutbu

---

值得一提的是，在这种情况下，传递 PARSE_DECLTYPES 不是可选的。 - jfs

@J.F.Sebastian：也可以使用PARSE_COLNAMES，但是这样你需要使用类似于SELECT timecol [timeobj] ...的SQL语句。 - unutbu

我在其他情况下（不是Python + SQLite）使用过自午夜以来的微秒。我不是很喜欢它，但它是合理的。我不知道可以像你在示例中所做的那样在查询中使用Python对象的能力。我可能会从你的建议开始。谢谢。 - jdmarino

@jdmarino：我在第一篇帖子中犯了一个错误。SQL查询没有利用Python对象。比较是在底层数据库值上进行的——这种值是由adapt_timeobj返回的。 - unutbu

Answer 2

我非常喜欢@unutbu的答案, 但这里有一种简单的方法来存储时间戳。 RFC 3339是一个非常明确的时间戳格式，易于计算机解析，也易于人类阅读。你可以将时间戳存储为字符串。

RFC 3339的一个好处是：简单的ASCII排序也可以按时间顺序排序。

但实际上你不需要规范，因为它非常简单。以下是一个示例：

2014-12-24T23:59:59.9999-08:00

这是在我的时区（比协调世界时慢8小时，因此有-08:00）的圣诞节前最后一刻。年、月、日、字符串T、小时、分钟、秒钟、可选小数秒和时区。

时区也可以是Z，表示协调世界时。但将时间存储在本地时区可能更方便，以便更容易阅读。

- steveha

---

rfc3339 要求 包含日期部分。OP 仅询问 datetime.time() -- 没有日期。此外，-08 是不正确的。应该是 -08:00。 - jfs

好的，这个问题并没有“禁止”存储日期，而且我认为RFC 3339有一些优势。你关于时区的观点是正确的，我已经修复了它；谢谢。 - steveha

感谢您的回答。我的数据集并不总是有日期，只有时间。我考虑了您的解决方案，但我需要添加一个“标准日期”作为占位符（例如1/1/1960）。这种方法的好处是排序（正如您指出的那样），而且我可以使用sqlite的日期处理函数。缺点是我已经表示了一个实际上不存在的日期。 - jdmarino

我不会去那么远，去添加一个假日期，当你只需要时间的时候。但是你可能想要标准化使用 RFC 3339 中所做的方式，仅使用时间部分：HH:MM:SS.fraction，其中 HH 是从 00 到 23 的两位数字小时，MM 和 SS 始终是两位数字（00 到 59），而 fraction 是可选的，但如果存在，则是表示秒分数的浮点值。易于阅读，并且 ASCII 排序是按时间排序。 - steveha