PostgreSQL Generate Date Ranges using recursive CTE

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s