Generate Series on Months with PostgreSQL

Generate a date serie for the last 24 months starting from the current date with PostgreSQL 9.3. The SQL is using generate_series.

select date_trunc('MONTH', monat)::DATE from 
    (select * from generate_series(now()- interval '24 month',now(),'1 month') as monat) as mt order by 1;

This is the output of the above SQL:

date_trunc ------------ 2013-06-01 2013-07-01 2013-08-01 2013-09-01 2013-10-01 2013-11-01 2013-12-01 2014-01-01 2014-02-01 2014-03-01 2014-04-01 2014-05-01 2014-06-01 2014-07-01 2014-08-01 2014-09-01 2014-10-01 2014-11-01 2014-12-01 2015-01-01 2015-02-01 2015-03-01 2015-04-01 2015-05-01 2015-06-01 (25 rows)

Now we use the generated date series and join those dates to the date which are present in the table realm. Again we used generate_series and for easier maintenance the WITH-Clause is used.

// accumulative count of items for the last 60 months.   source table realm, attribute: created

// generate as time series from now 60 months backwards
with monthserie as (select date_trunc('MONTH', monat)::DATE as monat from (
    select * from generate_series( now()- interval '60 month',now(),'1 month' ) as monat) as mt),

items as (
    // count items for the last 60 month for each month
    select date_trunc('MONTH', created)::DATE as crt,count(*) as cnt from realm 
         where date_trunc('MONTH', created)::DATE > date_trunc('MONTH', now())::DATE - interval '60 month' group by crt
    union
    // count items EARLIER than 60 month for each month
    select date_trunc('MONTH', now()- interval '60 month') as crt, count(*) as cnt from realm 
        where created <= date_trunc('MONTH', now())::DATE - interval '59 month' group by crt
 )
 select monat, sum(cnt) over (order by monat)
     from monthserie left join items on (monat = crt)
     group by monat,cnt;

Now we use the same SQL and return the result as JSON-String:

with monthserie as (select date_trunc('MONTH', monat)::DATE as monat from (
     select * from generate_series( now()- interval '60 month',now(),'1 month' ) as monat) as mt),
items as (
   select date_trunc('MONTH', created)::DATE as crt,count(*) as cnt from realm 
        where date_trunc('MONTH', created)::DATE > date_trunc('MONTH', now())::DATE - interval '60 month' group by crt
   union
   select date_trunc('MONTH', now()- interval '60 month') as crt, count(*) as cnt from realm 
       where created <= date_trunc('MONTH', now())::DATE - interval '59 month' group by crt
)
SELECT to_json( array_agg(t) ) from (
   select monat, sum(cnt) over (order by monat)
   from monthserie left join items on (monat = crt)
   group by monat,cnt
) as t;

Here is the output of the PostgreSQL 9.3 Query

monat | sum ------------+------ 2010-06-01 | 324 2010-07-01 | 342 2010-08-01 | 379 2010-09-01 | 396 2010-10-01 | 400 2010-11-01 | 410 2010-12-01 | 454 2011-01-01 | 476 2011-02-01 | 485 2011-03-01 | 496 2011-04-01 | 502 2011-05-01 | 512 2011-06-01 | 538 2011-07-01 | 549 2011-08-01 | 562 2011-09-01 | 575 2011-10-01 | 585 2011-11-01 | 592 2011-12-01 | 600 2012-01-01 | 605 2012-02-01 | 609 2012-03-01 | 615 2012-04-01 | 620 2012-05-01 | 627 2012-06-01 | 638 2012-07-01 | 648 2012-08-01 | 653 2012-09-01 | 660 2012-10-01 | 663 2012-11-01 | 667 2012-12-01 | 677 2013-01-01 | 683 2013-02-01 | 703 2013-03-01 | 711 2013-04-01 | 726 2013-05-01 | 739 2013-06-01 | 758 2013-07-01 | 774 2013-08-01 | 796 2013-09-01 | 821 2013-10-01 | 848 2013-11-01 | 863 2013-12-01 | 893 2014-01-01 | 942 2014-02-01 | 962 2014-03-01 | 986 2014-04-01 | 1004 2014-05-01 | 1022 2014-06-01 | 1070 2014-07-01 | 1116 2014-08-01 | 1170 2014-09-01 | 1189 2014-10-01 | 1195 2014-11-01 | 1209 2014-12-01 | 1212 2015-01-01 | 1215 2015-02-01 | 1227 2015-03-01 | 1234 2015-04-01 | 1239 2015-05-01 | 1250 2015-06-01 | 1250 (61 rows)

Please note that there were no addition in June 2015.

Change Content

The information on this page is user generated content. The content does not claim to be complete or correct.

Everybody is invited to add or change the data. Just click on this link. No login or email is required. Thanks.