Querying Date ranges in postgres

When we are trying to query a Date with an PostgresSQL query like:

SELECT d, name from table1
where d between '2021-10-01' and '2021-11-31';

You will notice your query in Postgres is not including the upper bond ie 2021-11-31.

Inorder to resolve this you can query it like below using DATE field with <= and >= operator. This will ensure both the dates are included in the upper bound and lower bound.

SELECT d, name from table1
where DATE(d) >= '2021-10-01' and DATE(d) <= '2021-11-31';

This query can be implemented in SQLAlchemy as following:

db.query(table1.d, table1.name)
.filter(
    func.date(table1.d)>='2021-10-01',
    func.date(table1.d)<='2021-11-31'
    )