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 4DE0B9DCBA9 for ; Fri, 24 Mar 2006 16:31:53 -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 43290-03 for ; Fri, 24 Mar 2006 16:31:53 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 4E6E09DC84D for ; Fri, 24 Mar 2006 16:31:51 -0400 (AST) Received: from outmx009.isp.belgacom.be (outmx009.isp.belgacom.be [195.238.5.4]) by svr4.postgresql.org (Postfix) with ESMTP id 0539E5AF0D0 for ; Fri, 24 Mar 2006 20:31:52 +0000 (GMT) Received: from outmx009.isp.belgacom.be (localhost [127.0.0.1]) by outmx009.isp.belgacom.be (8.12.11/8.12.11/Skynet-OUT-2.22) with ESMTP id k2OKVdhH003551 for ; Fri, 24 Mar 2006 21:31:43 +0100 (envelope-from ) Received: from [10.8.0.2] (62-162.244.81.adsl.skynet.be [81.244.162.62]) by outmx009.isp.belgacom.be (8.12.11/8.12.11/Skynet-OUT-2.22) with ESMTP id k2OKVU35003448 for ; Fri, 24 Mar 2006 21:31:31 +0100 (envelope-from ) From: MaXX To: pgsql-sql@postgresql.org Subject: generate_series to return row that doesn't exist in table... Date: Fri, 24 Mar 2006 21:30:56 +0100 User-Agent: KMail/1.9.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200603242130.57851.bs139412@skynet.be> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200603/389 X-Sequence-Number: 24735 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. Thanks for your help, -- MaXX