Обсуждение: Grouping by timestamp, how to return 0 when there's no record
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
On 19 September 2011 12:01, Koen Van Impe <koen.vanimpe@belnet.be> wrote:
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.
-- 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.
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!