我是否需要在 SQLite 中将 DateTime 数据存储为 julianday ？

Question

SQLite 文档指定在数据库中存储日期时间值的首选格式是使用儒略日（使用内置函数）。

但是，我在 python（pysqlite、SQLAlchemy）中看到的所有框架都将 datetime.datetime 值存储为 ISO 格式的字符串。他们为什么要这样做？

我通常尝试调整框架以将日期时间存储为儒略日，这非常痛苦。我开始怀疑这是否值得付出努力。

请与我分享您在该领域的经验。坚持朱利安日有意义吗？

Answer 1

儒略日对于各种日期计算都很方便，但它可以适当地存储时间部分（具有精确的小时、分钟和秒）。过去，我使用过儒略日字段（用于日期）和纪元秒数（用于 datetime 实例），但仅当我对计算（日期和时间）有特定需求时才使用。分别为次）。我认为 ISO 格式的日期和日期时间的简单性应该使它们成为首选，大约 97% 的情况下。

Answer 2

两种方式都可以保存。框架可以按照自己的方式设置，如果您希望找到带有 ISO 格式字符串的原始列，那么绕过它可能会更加痛苦，而不是值得的。

拥有两列的问题是数据一致性，但 sqlite 应该拥有使其工作所需的一切。 3.3 版支持检查约束和触发器。阅读日期和时间函数。您应该能够完全在数据库中执行您需要的操作。

CREATE TABLE Table1 (jd, isotime);

CREATE TRIGGER trigger_name_1 AFTER INSERT ON Table1
BEGIN
    UPDATE Table1 SET jd = julianday(isotime) WHERE rowid = last_insert_rowid();
END;

CREATE TRIGGER trigger_name_2 AFTER UPDATE OF isotime ON Table1
BEGIN
    UPDATE Table1 SET jd = julianday(isotime) WHERE rowid = old.rowid;
END;

如果您无法在数据库中执行您需要的操作，您可以编写 C 扩展来执行您需要的功能。这样，除了加载扩展之外，您不需要接触框架。

Answer 3

但通常情况下，人类不会直接从数据库中读取数据。儒略日的小数时间很容易转换为人类可读的（例如）

void hour_time(GenericDate *ConvertObject)
{
    double frac_time    = ConvertObject->jd;
    double hour         = (24.0*(frac_time - (int)frac_time));
    double minute       = 60.0*(hour - (int)hour);
    double second       = 60.0*(minute - (int)minute);
    double microsecond  = 1000000.0*(second - (int)second);

    ConvertObject->hour         = hour;
    ConvertObject->minute       = minute;
    ConvertObject->second       = second;
    ConvertObject->microsecond  = microsecond;

};

Answer 4

因为对于人类来说，2010-06-22 00:45:56 比 2455369.5318981484 更容易阅读。文本日期非常适合在 SQLiteSpy 或 SQLite Manager 中执行即席查询。

当然，主要缺点是文本日期需要 19 个字节而不是 8 个字节。