From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | koen(dot)vanimpe(at)belnet(dot)be |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Grouping by timestamp, how to return 0 when there's no record |
Date: | 2011-09-19 11:55:42 |
Message-ID: | CAF-3MvMQtx9f83zsW9iJqyyvzmOJJF4RHaG_OzPfNPXvWN4uGQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 19 September 2011 12:01, Koen Van Impe <koen(dot)vanimpe(at)belnet(dot)be> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Diego Augusto Molina | 2011-09-19 12:08:27 | Re: Grouping by timestamp, how to return 0 when there's no record |
Previous Message | Diego Augusto Molina | 2011-09-19 11:30:38 | Re: Clean way to insert records if they don't exist, update if they do |