I would like to print this table (displaying only 4 rows for brevity):

So far I have:

SELECT CAST(TRUNC(date_trunc('month',CURRENT_DATE)   interval '-2 month') AS DATE)   (n || 'day')::INTERVAL AS Dates
, date_trunc('month',CURRENT_DATE)   interval '-2 month'   INTERVAL '2 month' - date_trunc('month',CURRENT_DATE)   interval '-2 month' AS Period
FROM generate_series(0,61) n 

Please help with a better way of generating the period and also replacing the hard-coded 61 in generate_series(0,61).

Thanks!

CodePudding user response：

What are you actually trying to accomplish, it is not clear nor specified. BTW your query is invalid. It appears you looking to list each data from first date of 2 months prior to the last date of 1 month prior and the total number of days in that range. The following would give the first date, and using date subtraction gives the number of days.

with full_range( first_dt, num_days) as 
     ( select  date_trunc ('month', (current_date - interval '2 months'))::date
            ,  date_trunc ('month', (current_date - interval '1 day'))::date - 
               date_trunc ('month', (current_date - interval '2 months'))::date
     ) 
select * 
  from full_range;

With that in hand you can use the num_days with generate series with the expression

select generate_series(0, num_days-1) from full_range

Finally combine the above arriving at: (see demo)

with full_range( first_dt, num_days) as 
     ( select  date_trunc ('month', (current_date - interval '2 months'))::date
            ,  date_trunc ('month', (current_date - interval '1 day'))::date - 
               date_trunc ('month', (current_date - interval '2 months'))::date
     ) 
select (first_dt   n*interval '1 day')::date, num_days
  from full_range
  cross join (select generate_series(0, num_days-1) from full_range) gn(n);