Get the rows from a table filtered by current month in psql.
Example table –
x
+--------------+--------------------+--------------------------+
| id | name | registeration_date |
+--------------------------------------------------------------+
| 1 | abc | 2018|12|31 18:30:00|00 |
+--------------------------------------------------------------+
| 2 | pqr | 2018|11|31 18:30:00|00 |
+--------------------------------------------------------------+
| 3 | lmn | 2020|07|10 18:30:00|00 |
+--------------+--------------------+--------------------------+
After query result, assuming current month is July, 2020
:
+--------------+--------------------+--------------------------+
| id | name | registeration_date |
+--------------+--------------------+--------------------------+
| 3 | lmn | 2020|07|10 18:30:00|00 |
+--------------+--------------------+--------------------------+
Advertisement
Answer
The usual approach is to use a range query to allow for an index usage on registration_date
select *
from the_table
where registration_date >= date_trunc('month', current_date)
and registration_date < date_trunc('month', current_date) + interval '1 month';