pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: jan zimmek <jan.zimmek@web.de>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: need some magic with generate_series()
Date: Tue, 22 Jan 2013 22:49:56 +0100
Message-ID: <1B56ACCA-3259-4AF6-9EBA-7045E261B18F@web.de> (raw)
In-Reply-To: <50FF0784.1070205@gmx.net>
References: <50FF0784.1070205@gmx.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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 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

view thread (8+ messages)  latest in thread

Message-ID: <1B56ACCA-3259-4AF6-9EBA-7045E261B18F@web.de>
Permalink:  ../1B56ACCA-3259-4AF6-9EBA-7045E261B18F@web.de/
Also on:    postgresql.org/message-id/1B56ACCA-3259-4AF6-9EBA-7045E261B18F@web.de

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jan.zimmek@web.de, maps.on@gmx.net
  Subject: Re: need some magic with generate_series()
  In-Reply-To: <1B56ACCA-3259-4AF6-9EBA-7045E261B18F@web.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox