Title:
Tag1 for Path:	a single word in lower characters
Tag2 for Path:
List of alternative single tags:	one or many words in lower characters, separated by comma or space
Text:	<p>Generate a date serie for the last 24 months starting from the current date with PostgreSQL 9.3. The SQL is using generate_series.</p> <pre> 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; </pre> <p>This is the output of the above SQL:</p> <samp> 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) </samp> <p>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.</p> <pre> // 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; </pre> <p>Now we use the same SQL and return the result as JSON-String:</p> <pre> 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; </pre> <p>Here is the output of the PostgreSQL 9.3 Query</p> <samp> 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) </samp><p>Please note that there were no addition in June 2015.</p>
URL:
Operation:	Delete Update Insert
Template Prompt