Nathan Thatcher wrote:
I have, what I imagine to be, a fairly simple question. I have a query
that produces output for a line graph. Each row represents an interval
on the graph.
SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
FROM c_call WHERE start_time >= '2008-08-01 00:00:00' AND end_time <=
'2008-08-01 23:59:59' GROUP BY hour
This works great when there is data in each interval but when a given
interval has no data the group is omitted. What is the best way to
ensure that the result contains a row for each interval with the value
field set to zero or null? The reporting tool is incapable of filling
in the gaps.
Thanks
Use generate_series as part of your query. You can get a listing of all
the hours, which can be integrated with your other data in a variety of
ways, using:
select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1
hour'::interval as hour;
hour
---------------------
2008-08-01 00:00:00
2008-08-01 01:00:00
...
2008-08-01 23:00:00
