Trying to accommodate relative defined date, such as 5 days ago, into my fixed date condition in PostgreSQL

Question

I'm trying to condition my WHERE clause to accommodate relatively defined dates into my date filter. I'm pretty confused what type I need to use, if it's CONVERT or TO_DATE function, or if I need to put a CASE WHEN statement into my code.

This is the code that I have written so far:

WHERE event_create_verified_user_success.created_at_utc_date
BETWEEN DATE '2021-11-29' AND DATE '2021-12-05'

And this is the condition of the activity I need to finish:

If the desired date-period is not set manually using fixed dates like from “2021-11-29” to “2021-12-05”, how would you change the where-clause to consider all data from relative defined dates: “consider messages created between 10 days and 5 days ago (inclusive)”

I've only started PostgreSQL yesterday and the last time I've handle SQL was probably 4 years ago so I'm pretty confused at how much SQL has changed since then.

Thank you so much for helping!

Answer 1

The basic syntax hasn't really changed in the last 4 years (or even 15 years).

You can use current_date to obtain "today's date". You can subtract days from that

where ... between current_date - 10 and current_date - 5

If created_at_utc_date is a timestamp (= date and time) rather than a date (=date without time) it's better to use a range query though:

where created_at_utc_date >= current_date - 10
  and created_at_utc_date < current_date - 4

Note the < combined with the next day you want to compare with.