From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
---|---|
To: | MaXX <bs139412(at)skynet(dot)be> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: generate_series to return row that doesn't exist in |
Date: | 2006-03-24 20:42:33 |
Message-ID: | 1143232952.3625.6.camel@state.g2switchworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2006-03-24 20:46:55 | Re: Find min and max values across two columns? |
Previous Message | MaXX | 2006-03-24 20:30:56 | generate_series to return row that doesn't exist in table... |