Python/Sqlalchemy/Sqlite - How to add datetime field and integer seconds (timedelta) in where condit-CodePudding

I have a sqlalchemy/sqlite table:

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

Now I would like to construct the where condition in which I want to check whether field_dt field_int (seconds) <= utc_now. Something like: select(MyTable).where(?).

With no sqlalchemy/sqlite I would construct condition like this:

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')

Output:

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

How to do the same with sqlalchemy/sqlite

CodePudding user response：

SQLite 3.38.0 implements a unixepoch function that can convert a datetime to a Unix timestamp, so in theory we could do

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)
)

however 3.38.0 was released on 2022-02-22 so at the time of writing it may not be widely distributed.

If unixepoch is not available we can use SQLite's datetime function to construct a new datetime. The SQL would look something like this:

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

the SQLAlchemy equivalent is:

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

If field_dt is indexed, consider moving the modifier to the RHS of the inequality:

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

It may be worth considering storing the datetime as a Unix timestamp to simplify the query.

The SQLite date functions documentation is here.