Python/Sqlalchemy/Sqlite - 如何在条件下添加日期时间字段和整数秒（timedelta）？

Question

我有一个 sqlalchemy/sqlite 表：

class MyTable(Base):
    __tablename__ = 'mytable'
    ...
    field_dt = Column(DateTime)
    field_int = Column(Integer, default=0)

现在我想构造 where 条件，在其中我想检查 field_dt + field_int (秒) <= utc_now。 类似于：select(MyTable).where(?)。

如果没有 sqlalchemy/sqlite，我会构造如下条件：

import datetime as dt

utc_now = dt.datetime(2022,3,2,1,0,10)
field_dt = dt.datetime(2022,3,1,1,0,5)
field_int = 60

print(f"  utc_now = {utc_now.isoformat()}")
print(f" field_dt = {field_dt.isoformat()}")
print(f"field_int = {field_int}")

if field_dt + dt.timedelta(seconds=field_int) < utc_now:
    print('it is less than utc_now')

输出：

  utc_now = 2022-03-02T01:00:10
 field_dt = 2022-03-01T01:00:05
field_int = 60
it is less than utc_now

如何对 sqlalchemy/sqlite 执行相同操作

Answer 1

SQLite 3.38.0 实现了一个 unixepoch 函数，可以将日期时间转换为 Unix 时间戳，因此理论上我们可以这样做

import sqlalchemy as sa
# Untested
q = sa.select(MyTable).where(
    (sa.func.unixepoch(MyTable.field_dt) + MyTable.field_int)
    < sa.func.unixepoch(dt.datetime.utcnow)
)

，但是 3.38.0 于 2022 年 2 月 22 日发布，所以在编写它时可能不会广泛分布。

如果unixepoch不可用，我们可以使用SQLite的datetime函数来构造一个新的datetime。 SQL 看起来像这样：

select datetime(field_dt, '+' || cast(field_int as text) || ' seconds') as dt
from mytable
where dt < datetime('now');

SQLAlchemy 等价的是：

q = sa.select(MyTable).where(
    sa.func.datetime(
        MyTable.field_dt,
        '+' + sa.cast(MyTable.field_int, sa.String) + ' seconds',
    )
    < dt.datetime.utcnow()
)

如果 field_dt 被索引，请考虑将修饰符移动到不等式的 RHS：

q = sa.select(MyTable).where(
    MyTable.field_dt
    < sa.func.datetime(
        dt.datetime.utcnow(),
        '-' + sa.cast(MyTable.field_int, sa.String) + ' seconds',
    )
)

可能值得考虑将日期时间存储为 Unix 时间戳以简化查询。

SQLite 日期函数文档位于此处。