My Database Friend PostgreSQL

Next: Move Table to new Owner and Schema UP: TOC

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.

Next: Move Table to new Owner and Schema UP: TOC