Querying Date ranges in postgres
When we are trying to query a Date with an PostgresSQL query like:
1
SELECT d, name from table1
2
where d between '2021-10-01' and '2021-11-31';
Copied!
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.
1
SELECT d, name from table1
2
where DATE(d) >= '2021-10-01' and DATE(d) <= '2021-11-31';
Copied!
This query can be implemented in SQLAlchemy as following:
1
db.query(table1.d, table1.name)
2
.filter(
3
func.date(table1.d)>='2021-10-01',
4
func.date(table1.d)<='2021-11-31'
5
)
Copied!
Last modified 1mo ago
Copy link