One of the tasks that I had needed me to have a proxy table of dates using which I had to achieve other business logic. Using PostgreSQL and recursive CTE I was able to achieve this.
Here is the code –
--cte to get range of numbers, which in this case is 1 to 90 with recursive ints (n) AS ( select 1 union all select n+1 from ints where n+1<= 90 ) --cte to get date ranges by using interval function and --multiplying the ranges ,cte_dates as ( select current_date - interval '90days' + interval '1day' * n as dt from ints ) --the query below will now get you last 90 days date ranges select dt from cte_dates</pre> <pre>
The point to note is you can multiply the intervals that you specify using the good old multiplier.