Thread: generate_series to return row that doesn't exist in table...
Hi, I have a table wich contains aggregated data, table stats_activitylogtime 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
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
On Friday 24 March 2006 21:42, Scott Marlowe wrote: > 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 Perfect!! Time to RTFM again... Thanks, -- MaXX