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?

AdamGold
  • 4,941
  • 4
  • 29
  • 47
  • Not without jumping through some serious hoops. SQLite does not have an interval type, but instead uses functions for such arithmetic: https://www.sqlite.org/lang_datefunc.html (check using modifiers). You could create a custom SQLA construct that'll compile to suitable expression on both DBMS. – Ilja Everilä Jul 31 '19 at 14:27
  • I see. Do you think there's any implementation of that qurey logic that does not require `INTERVAL`? @IljaEverilä – AdamGold Jul 31 '19 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](https://stackoverflow.com/questions/2716847/sqlalchemy-sqlite-for-testing-and-postgresql-for-development-how-to-port), but if you want to, check this Q/A for pointers on making custom constructs: https://stackoverflow.com/questions/46907724/sqlalchemy-get-row-in-timeslot – Ilja Everilä Aug 01 '19 at 06:22

0 Answers0