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

Поиск
Список
Период
Сортировка
От MaXX
Тема generate_series to return row that doesn't exist in table...
Дата
Msg-id 200603242130.57851.bs139412@skynet.be
обсуждение исходный текст
Ответы Re: generate_series to return row that doesn't exist in  (Scott Marlowe <smarlowe@g2switchworks.com>)
Список pgsql-sql
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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Amos Hayes
Дата:
Сообщение: Find min and max values across two columns?
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: generate_series to return row that doesn't exist in