Postgresql Generate_Series of Months

How to generate Month list in PostgreSQL?

You can generate sequences of data with the generate_series() function:

SELECT to_char(generate_series(min, max, '1 month'), 'Mon-YY') AS "Mon-YY"
FROM (
  SELECT date_trunc('month', min(startdate)) AS min, 
         date_trunc('month', max(startdate)) AS max
  FROM a) sub;

This generates a row for every month, in a pretty format. If you want to have it like a list, you can aggregate them all in an outer query:

SELECT string_agg("Mon-YY", ', ') AS "Mon-YY list"
FROM (
  -- Query above
) subsub;

SQLFiddle here

generate series based on particular day in each month -postgresql

You cannot accomplish what you want with generate_series. This results due to that process applying a fixed increment from the previous generated value. Your case 1 month. Now Postgres will successfully compute correct end-of-month date from 1 month to the next. So for example 1month from 31-Jan yields 28-Feb (or 29), because 31-Feb would be an invalid date, Postgres handles it. However, that same interval from 28-Feb gives the valid date 28-Mar so no end-of-month adjustment is needed. Generate_Series will return 28th of the month from then on. The same applies to 30 vs. 31 day months.

But you can achieve what your after with a recursive CTE by employing a varying interval to the same initial start date. If the resulting date is invalid for date the necessary end-of-month adjustment will be made. The following does that:

create or replace function constant_monthly_date
                         ( start_date timestamp
                         , end_date   timestamp
                         ) 
  returns setof date 
  language sql strict
as $$
with recursive date_set as
     (select start_date ds, start_date sd, end_date ed, 1 cnt
      union all
      select (sd + cnt*interval '1 month')  ds, sd, ed, cnt+1 
       from date_set
      where ds<end_date
     )
select ds::date from date_set;    
$$;

-- test
select * from constant_monthly_date(date '2020-01-15', date '2020-12-15' );
select * from constant_monthly_date(date '2020-01-31', date '2020-12-31' );

Use generate_series to populate list of months for a given year in PostgreSQL

SELECT distinct to_char(dd, 'Month') FROM generate_series(to_date('2013', 'YYYY') , date_trunc('month', now()), '1 month') as dd;

PostgreSQL generate month and year series based on table field and fill with nulls if no data for a given month

Put the generate_series() in the FROM clause. You are summarizing the data -- i.e. calculating the price over the entire range -- and then projecting this on all months. Instead:

SELECT gs.yyyymm,
       coalesce(SUM(i.price), 0) 
FROM generate_series('2019-03-01'::date, '2020-02-01', INTERVAL '1 MONTH'
                    ) gs(yyyymm) LEFT JOIN
     items i
     ON gs.yyyymm = DATE_TRUNC('month', s.date_added) AND
       i.item_type_id = 3 
GROUP BY gs.yyyymm
ORDER BY gs.yyyymm; 

generate_series for creating a list of months to get missing dates

Generate all combinations of dates per student_id with a cross join and then left join the original table on that to get missing rows with 0 values.

select i.student_id,m.mth,coalesce(t.attendance,0)
from (select distinct student_id from tbl) i
cross join generate_series('2017-01-01'::date,'2017-12-01'::date, '1 MONTH') m(mth)
left join tbl t on t.student_id=i.student_id and t.dt=m.mth

---

Related Topics