X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id C23FC9DCC3B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 16:42:34 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 44849-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 16:42:35 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 853D09DCD3F
	for <pgsql-sql@postgresql.org>; Fri, 24 Mar 2006 16:42:32 -0400 (AST)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Fri, 24 Mar 2006 20:42:33 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	24 Mar 2006 14:42:33 -0600
Subject: Re: generate_series to return row that doesn't exist in
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: MaXX <bs139412@skynet.be>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200603242130.57851.bs139412@skynet.be>
References: <200603242130.57851.bs139412@skynet.be>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1143232952.3625.6.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Fri, 24 Mar 2006 14:42:33 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.143 required=5 tests=[AWL=0.142,
	UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 0.143
X-Spam-Level: 
X-Archive-Number: 200603/390
X-Sequence-Number: 24736

On Fri, 2006-03-24 at 14:30, MaXX wrote:
> Hi,
> 
> I have a table wich contains aggregated data, 
> table stats_activity
> 	logtime timestamptz,
> 	count int
> 
> given this dataset
> "2006-03-24 03:00:00+01";55
> "2006-03-24 04:00:00+01";33
> "2006-03-24 06:00:00+01";46
> "2006-03-24 07:00:00+01";63
> "2006-03-24 08:00:00+01";88
> 
> I want to get this in order to plot the data
> "2006-03-24 03:00:00+01";55
> "2006-03-24 04:00:00+01";33
> >>"2006-03-24 05:00:00+01";0<<
> "2006-03-24 06:00:00+01";46
> "2006-03-24 07:00:00+01";63
> "2006-03-24 08:00:00+01";88
> 
> I used generate_series to get all the timestamps I need but I don't know how 
> to write my query.
> 
>  
> I've tried various combination of subselects, joins, union,... and I never 
> managed to get the result I wanted... I'm sure the solution is trivial but I 
> don't get it...
> I prefer to generate missing rows "on the fly" intead of actually storing 
> useless data on the table.

More than likely you need a left join and a case statement.

select <selectlist>, case when a.date is null then 0 else a.date end 
from (select * from generate_series() -- magic to get dates goes here)
as p left join maintable as a on (p.date=a.date);

There may be some small syntax error in there, as I've not tested it. 
The relavent pages are:

case:
http://www.postgresql.org/docs/8.1/static/functions-conditional.html

joins:
http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html