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

Postgresql – Selecting a range of days across multiple years

I have a problem that involves the WHERE clause of my query:

...
WHERE distinct_policy.policy_created_date_time between '2023-03-01' and '2023-03-11'

The column "policy_created_date_time" is a timestamp without time zone. I want to select only ten days (first March – tenth March), but in any year (not just in 2023).

Is there any simple way to do it, because sql doesn’t allow me just to delete the year.

Tags:

2

Answers


  1. 0

    One option is to check on months and days, by extracting directly those parts, using EXTRACT.

    ...
WHERE EXTRACT(MONTH FROM distinct_policy.policy_created_date_time) = 3 
  AND EXTRACT(DAY FROM distinct_policy.policy_created_date_time) BETWEEN 1 AND 10
    Login or Signup to reply.
  2. 0

    You can use generate_series and a range data type to solve this issue. You now can also use an (existing) index on your column.

    This example takes the next 100 years, you could take a different start or end.

    WITH target AS (
    SELECT  tsrange(t::date,    t::date + 10, '[]') r
    FROM    generate_series('2023-03-01'::timestamp, '2023-03-01'::timestamp + INTERVAL '100 years', INTERVAL '1 year' ) g(t)
)
SELECT t.*
FROM t -- your table
  JOIN target ON r @> policy_created_date_time;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search