1

Trying to have a single query to work both on PostgreSQL and SQLite - I need that query to compare datetime by adding an iterval using a database column:

end_date = date + timedelta(minutes=duration)
appointment_end_date = Appointment.date + func.make_interval(
    0, 0, 0, Appointment.duration
)
existing_lessons = Appointment.query.filter(
        or_(
            and_(Appointment.date <= end_date, Appointment.date >= date),
            and_(appointment_end_date >= date, appointment_end_date <= end_date),
        )
).all()

The query is trying to find any row that the date variable is inside Appointment.date to Appointment.date + Appointment.duration. If I could write it using timedelta, here's what I would write:

Appointment.date + timedelta(minutes=Appointment.duration)

Currently it only works for PostgreSQL:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: make_interval

Any chance of making such a query to work with both databases?

Improve this question
3
  • Not without jumping through some serious hoops. SQLite does not have an interval type, but instead uses functions for such arithmetic: sqlite.org/lang_datefunc.html (check using modifiers). You could create a custom SQLA construct that'll compile to suitable expression on both DBMS. Commented Jul 31, 2019 at 14:27
  • I see. Do you think there's any implementation of that qurey logic that does not require INTERVAL? @IljaEverilä Commented Jul 31, 2019 at 14:37
  • 1
    Well, you could check if the difference (interval) is greater or equal to date - Appointment.date, but that does not buy you much, as you'd still have to have different queries for Postgresql and SQLite. This and other such reasons are why people usually just advice against using different DBMS, but if you want to, check this Q/A for pointers on making custom constructs: stackoverflow.com/questions/46907724/… Commented Aug 1, 2019 at 6:22
Related questions
0
python sql interval
2
Subclass Interval for dateutil.relativedelta support in SQLAlchemy
0
Using parameter with interval in query
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.