Question 315735 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Use the previous 1st of October of the current date in a query

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?

Tags:

2

Answers


  1. 0

    Here is how to get the last specific month (in your question you need to get the last october) :

    SELECT (extract(year from current_date) || '-10-01')::date
       - (case when extract(month from current_date) < 10 then 1 else 0 end || ' year')::interval  
AS last_october;

    For today 2024-01-07 it will results :

    last_october
2023-10-01 00:00:00+00

    So your query will be :

    SELECT *
FROM mytable
WHERE (start_date, end_date) OVERLAPS (
                               (extract(year from current_date) || '-10-01')::date
                                - (case when extract(month from current_date) < 10 then 1 else 0 end || ' year')::interval
                                , CURRENT_DATE
                             );

    (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

    Login or Signup to reply.
  2. 0

    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, then make_date(y,m,d) out of it. Demo at db<>fiddle:

    create table mytable (start_date date,end_date date);
insert into mytable values
('2023-01-07','2025-01-07'),
('2021-01-07','2022-01-07');

SELECT *  FROM mytable
WHERE    (start_date,end_date) 
OVERLAPS (make_date(extract(year from CURRENT_DATE-'9 months 1d'::interval)::int,10,1), 
          CURRENT_DATE);
    start_date end_date
    2023-01-07 2025-01-07

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search