Generate series
Uit Yapf
Op PHPFreakz had iemand het probleem dat hij moest weten welke hoeveel dagen van elke maand tussen twee datums zaten.
Het aantal dagen tussen twee datums is eenvoudig:
SELECT '2007-02-07'::date - '2007-01-06'::date;
geeft 32
Het probleem van welke maanden er tussen zitten is een heel ander verhaal want dat heeft het probleem dat de begin- en einddatums niet perse op de eerste van de maand vallen. Het zou eenvoudiger zijn als alle data tussen de grensdata beschikbaar waren... ohja, generate_series()!
Om vanaf een datum één dag verder te gaan kun je er gewoon een integer bij optellen. Generate_series genereert integers. Dus om tien dagen verder te gaan inclusief de startdatum:
SELECT '2007-02-07'::date + generate_series(0, 9); 7-2-2007 8-2-2007 9-2-2007 10-2-2007 11-2-2007 12-2-2007 13-2-2007 14-2-2007 15-2-2007 16-2-2007
Geeft tien records met datums van 2007-02-07 t/m 2007-02-17. Daar zitten dus alle datums in die we zoeken.
Die '10' is eenvoudig te vervangen met het aantal dagen tussen de twee datums:
SELECT '2007-02-07'::date + generate_series(0, ('2007-02-07'::date - '2007-01-06'::date))
Het ophalen van de maand is een kwestie van DATE_PART(). Tellen per maand is GROUP BY, dat levert op:
SELECT
COUNT(*)
, DATE_PART('month', series.daydate)
FROM (SELECT CURRENT_DATE + GENERATE_SERIES ( 1, 50 )) AS
series(daydate)
GROUP BY DATE_PART('month', series.daydate)
ORDER BY 2;
count date_part
11 6
31 7
8 8
Dat laat alleen nog het probleem dat een periode ook over meerdere jaren kan spannen, dus het jaartal moet ook in de group-by. Hiervoor zou je weer DATE_PART() kunnen gebruiken, maar er is ook DATE_TRUNC(), daarmee kun je een datum afronden tot een element van de datum. DATE_TRUNC('month', datumveld) levert een de eerste seconde van de eerste dag van de maand op. Groeperen daarop omvat dus ook het jaartal.
SELECT
COUNT(*)
, DATE_TRUNC('month', series.daydate)
FROM (SELECT CURRENT_DATE + GENERATE_SERIES (0, ('2008-02-07'::date
- '2007-01-06'::date))) AS series(daydate)
GROUP BY DATE_TRUNC('month', series.daydate)
ORDER BY 2;
count date_trunc
12 2007-06-01 00:00:00+02
31 2007-07-01 00:00:00+02
31 2007-08-01 00:00:00+02
30 2007-09-01 00:00:00+02
31 2007-10-01 00:00:00+02
30 2007-11-01 00:00:00+01
31 2007-12-01 00:00:00+01
31 2008-01-01 00:00:00+01
29 2008-02-01 00:00:00+01
31 2008-03-01 00:00:00+01
30 2008-04-01 00:00:00+02
31 2008-05-01 00:00:00+02
30 2008-06-01 00:00:00+02
20 2008-07-01 00:00:00+02
En dada, een kolom met het jaartal + maandnummer, en een kolom met hoeveel van de dagen tussen de twee datums in die maand van dat jaar vallen.