在SQLite中将DateTime数据存储为julianday值得吗？

Question

SQLite的文档说明，存储日期时间值的推荐格式是使用儒略日（通过内置函数）。

不过，我看到的所有Python框架（比如pysqlite和SQLAlchemy）都是把datetime.datetime值存储为ISO格式的字符串。为什么它们要这么做呢？

我通常试图调整这些框架，让它们存储日期时间为儒略日，但这过程相当麻烦。我开始怀疑这样做是否值得。

请和我分享一下你在这方面的经验。坚持使用儒略日有意义吗？

Answer 1

但通常情况下，人类并不会直接从数据库中读取数据。朱利安日的分数时间可以很容易地转换成人类可以理解的格式，比如说：

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 2

儒略日对于各种日期计算非常有用，但它不能很好地存储时间部分（比如精确到小时、分钟和秒）。过去我使用过儒略日字段（用于日期）和从纪元开始的秒数（用于datetime实例），但这只是当我有特定的计算需求时（分别是日期和时间的计算）。我认为，ISO格式的日期和时间的简单性应该使它们成为大约97%的情况下的首选。

Answer 3

可以双向存储数据。很多框架都有自己的处理方式，如果你的框架需要找到一个原始的、格式为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语言扩展来实现你需要的功能。这样，你只需要加载你的扩展，而不需要改动框架的其他部分。