generate_series to return row that doesn't exist in table...

From: MaXX <bs139412(at)skynet(dot)be>
To: pgsql-sql(at)postgresql(dot)org
Subject: generate_series to return row that doesn't exist in table...
Date: 2006-03-24 20:30:56
Message-ID: 200603242130.57851.bs139412@skynet.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Scott Marlowe 2006-03-24 20:42:33 Re: generate_series to return row that doesn't exist in
Previous Message Amos Hayes 2006-03-24 20:19:38 Find min and max values across two columns?