Grouping by timestamp, how to return 0 when there's no record

From: Koen Van Impe <koen(dot)vanimpe(at)belnet(dot)be>
To: pgsql-general(at)postgresql(dot)org
Subject: Grouping by timestamp, how to return 0 when there's no record
Date: 2011-09-19 10:01:57
Message-ID: 4E771315.3050409@belnet.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm trying to write a query that groups records by hour.
This works fine but when there are no records for a specific hour the
query does not return a result (this seems 'logic') and I'd like it to
return '0'. I suspect I should play around with 'interval' or something
but I can't get it to work.

I'd like to see a resultset similar to this
20 | 2011-09-12 12:00:00
7 | 2011-09-12 13:00:00
0 | 2011-09-12 14:00:00
14 | 2011-09-12 15:00:00

The current query is
SELECT COUNT(*) AS qt, DATE_TRUNC('hour',timestamp) as dfilter FROM
record WHERE record.timestamp BETWEEN (CURRENT_TIMESTAMP + INTERVAL '-7
day') and (CURRENT_TIMESTAMP) GROUP BY dfilter ORDER BY dfilter

Any help is highly appreciated.

kr,

koen

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vincent de Phily 2011-09-19 10:18:58 Re: duplicate sequence, it is possible?
Previous Message Rueegg Alexander 2011-09-19 09:40:44 Re: Dblink upgrade from 8.4 to 9.1