Using PostgresSQL INTERVAL in SQLAlchemy where the duration is dynamically stored in DB and is not a parameter

Question

This is not a duplicate of this question, because that question is/can be solved using a parameter or constant interval. In my case the interval is defined in a table. My intuition says the following should work:

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import INTERVAL
...
company_uuid = 'some_uuid'
query = db.session.query(CompanyFlagEntity)\
    .join(CompanyFlagTypeEntity)\  # implicit join using fk
    .filter(CompanyFlagEntity.company_uuid == company_uuid)\
    .filter((func.now() - INTERVAL(CompanyFlagTypeEntity.default_lookback_days, 'DAY')) <= CompanyFlagEntity.flag_date)

But I get a the following error:

AttributeError: 'INTERVAL' object has no attribute 'comparator'

The SQL version would be:

select company_flag.*
from company_flag
join company_flag_type on company_flag_type.uuid = company_flag.company_flag_type_uuid
where
    company_flag.company_uuid = 'my_uuid' and 
    (now() - (company_flag_type.default_lookback_days || ' days')::interval) <= flag_date

You could also use make_interval(): stackoverflow.com/questions/31362484/… — Ilja Everilä
– Ilja Everilä, Commented Jul 30, 2019 at 9:24
@IljaEverilä That is very clever. It works nicely, and is less hacky than my solution. If you want credit I'll upvote and accept your answer. If not I can include it in my own answer if you'd like. — André C. Andersen
– André C. Andersen, Commented Jul 31, 2019 at 7:43
I have added an answer to the question you linked that might give you another lead: stackoverflow.com/a/76804461/160665 I don't know if it is possible though as I doubt that the dynamic value can be used in the timedelta call... which is why I post this as a comment instead of an answer ;) — exhuma
– exhuma, Commented Jul 31, 2023 at 14:18

André C. Andersen · Accepted Answer · 2019-07-30 08:45:59Z

9

The solution fell out of the SQL when I wrote it manually:

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import INTERVAL
from sqlalchemy.sql.functions import concat
...
company_uuid = 'some_uuid'
query = db.session.query(CompanyFlagEntity)\
    .join(CompanyFlagTypeEntity)\  # implicit join using fk
    .filter(CompanyFlagEntity.company_uuid == company_uuid)\
    .filter((func.now() - func.cast(concat(db_base_app.CompanyFlagTypeEntity.default_lookback_days, ' DAYS'), INTERVAL)) <= cls.flag_date)

This referenced helped to find out how to create the interval dynamically in the first place.

answered Jul 30, 2019 at 8:45

André C. Andersen

9,5053 gold badges59 silver badges84 bronze badges

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

Collectives™ on Stack Overflow

Using PostgresSQL INTERVAL in SQLAlchemy where the duration is dynamically stored in DB and is not a parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related