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

Question

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

snakecharmerb · Accepted Answer · 2022-03-03 07:47:49Z

1

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.

answered Mar 3, 2022 at 7:47

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3225309 Over a year ago

What to do if field_dt is null? In that case I would like to skip this where condition?

snakecharmerb Over a year ago

To skip it entirely add an additional where - where(MyTable.field_int.is_not(None)); to convert the null to zero, use coalesce.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related