Thanks Filip,
with your help I came a step further. :)
Could I do the folowing without using a function?
CREATE OR REPLACE FUNCTION month_series ( date )
RETURNS table ( monthnr integer )
AS
$BODY$
select to_char ( m, 'YYYYMM' )::integer
from generate_series ( $1, current_date, '1 month'::interval )
as m
$BODY$ LANGUAGE sql STABLE;
select project_id, month_series ( createdate )
from projects
order by 1, 2;
Am 22.01.2013 22:52, schrieb Filip Rembiałkowski:
or even
select m from generate_series( '20121101'::date, '20130101'::date, '1
month'::interval) m;
On Tue, Jan 22, 2013 at 3:49 PM, jan zimmek <jan.zim...@web.de> wrote:
hi andreas,
this might give you an idea how to generate series of dates (or other
datatypes):
select g, (current_date + (g||' month')::interval)::date from
generate_series(1,12) g;
regards
jan
Am 22.01.2013 um 22:41 schrieb Andreas <maps...@gmx.net>:
Hi
I need a series of month numbers like 201212, 201301 YYYYMM to join other
sources against it.
I've got a table that describes projects:
projects ( id INT, project TEXT, startdate DATE )
and some others that log events
events( project_id INT, createdate DATE, ...)
to show some statistics I have to count events and present it as a view with
the project name and the month as YYYYMM starting with startdate of the
projects.
My problem is that there probaply arent any events in a month but I still need
this line in the output.
So somehow I need to have a select that generates:
project 7,201211
project 7,201212
project 7,201301
It'd be utterly cool to get this for every project in the projects table with
one select.
Is there hope?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
