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

2011-09-19 Thread Koen Van Impe
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Grouping by timestamp, how to return 0 when there's no record

2011-09-19 Thread Alban Hertroys
On 19 September 2011 12:01, Koen Van Impe  wrote:

> 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.
>

If you're working with an interval of time that's known when issuing the
query, you can add the missing hours by joining your results with
generate_series().

If not, you'll probably have to create a table with enough dates and hours
to join against, for which generate_series() is once again quite
convenient. I'd like to see a resultset similar to this

It works like this:
select hours.h from generate_series(0, 23) AS hours(h);

Alban.
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


Re: [GENERAL] Grouping by timestamp, how to return 0 when there's no record

2011-09-19 Thread Diego Augusto Molina
Hi, this may be a start:

  -- This will make our day better :)
with base_query (tstmp) as (
select DATE_TRUNC('hour',timestamp) as tstmp
  FROM record
  WHERE
record.timestamp BETWEEN ( CURRENT_TIMESTAMP + INTERVAL '-7 day') and
(CURRENT_TIMESTAMP) -- this I don't understand
  )
  -- The following will make the same as your query
select count(*) as qt, tstmp as dfilter from base_query
  -- And this adds the zero's
union
select 0 as qt, min(tstmp) + interval generate_series( 0, max(tstmp) -
min(tstmp) ) || ' hours' as dfilter
  from base_query
  where dfilter not in tstmp; -- Only for those who don't!

Don't forget to tell us how you ended up!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general