Обсуждение: need some magic with generate_series()
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?
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.on@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 startingwith 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
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.zimmek@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.on@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 YYYYMMstarting 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
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.zimmek@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.on@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
YYYYMMstarting 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