How to Efficiently Filter SQLAlchemy Date Fields by Range?

SQLAlchemy Date Field Filtering

Filtering date fields in SQLAlchemy allows for precise selection of records within specified date ranges. Consider the following model:

class User(Base):
    ...
    birthday = Column(Date, index=True)   #in database it's like '1987-01-17'
    ...

Filtering a Date Range

To filter users within a specific age range, you can leverage SQLAlchemy's filtering mechanism:

query = DBSession.query(User).filter(
    and_(
        User.birthday >= '1988-01-17',
        User.birthday <= '1985-01-17'
    )
)

However, this approach contains a typo. To select users aged between 18 and 30, you should swap the greater than or equal to (>=) and less than or equal to (<=) operators:

query = DBSession.query(User).filter(
    and_(
        User.birthday <= '1988-01-17',
        User.birthday >= '1985-01-17'
    )
)

Using Between

An alternative approach is to use the between method, which allows for direct specification of the date range:

query = DBSession.query(User).filter(
    User.birthday.between('1985-01-17', '1988-01-17')
)

The above is the detailed content of How to Efficiently Filter SQLAlchemy Date Fields by Range?. For more information, please follow other related articles on the PHP Chinese website!