CODEWITHSUNDEEP
sqlpostgresqldate
Oleh Rybalchenko
Oleh Rybalchenko

Reputation: 8059

How to get list of all dates between selected and n days before?

Assume we have date selected e.g '2017-07-05'. Using PostgreSQL, how to get list of all dates between selected and e.g. 20 days before? So expected following output:

2017-07-05
2017-07-04
2017-07-03
...
2017-06-15

In other words, I get :selected_date and :offset as params and need to output all dates in the range.

Upvotes: 0

Views: 41

Answers (2)

Oleh Rybalchenko
Oleh Rybalchenko

Reputation: 8059

Finally this works for me. I di it simultaneously, but a bit different way, so maybe it helps someone too

select '2017-07-01'::date - d.date as selected_date
  from generate_series(0, 20) as d(date)
order by selected_date desc

Upvotes: 0

user330315
user330315

Reputation:

You can use generate_series() for this:

select dt::date 
from generate_series(date '2017-07-05' - interval '20' day, date '2017-07-05', interval '1' day) as t(dt)
order by dt::date desc;

The cast dt::date is necessary because generate_series() returns a timestamp not a date

Upvotes: 3

Related Questions