I currently query data like this:
SELECT *
FROM mytable
WHERE (start_date, end_date) OVERLAPS ('2023-10-01'::DATE, CURRENT_DATE);
The problem is that I have to update this query every year. Is there a way to just use the previous 1st of October of the current date in a query?
2
Answers
Here is how to get the last specific month (in your question you need to get the last october) :
For today 2024-01-07 it will results :
So your query will be :
(extract(year from current_date) || '-10-01')::date
retrieves the first of October of the present year. Afterward, subtract one year if the current month is earlier than October, or maintain the current year if the month is October or later.Demo here
Subtract
9 months 1 day
from your date: up to October 1st it’ll move back to the previous year, or remain in the same year if it’s after October 1st.Extract()
the resulting year, thenmake_date(y,m,d)
out of it. Demo at db<>fiddle:If on October 1st you only want to target that one day, as opposed to the whole year since previous October 1st, you can subtract just the
9 months
.