From: MaXX <bs139412@skynet.be>
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
Message-ID: <200603242130.57851.bs139412@skynet.be> (raw)
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
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: bs139412@skynet.be
Subject: Re: generate_series to return row that doesn't exist in table...
In-Reply-To: <200603242130.57851.bs139412@skynet.be>
* 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