Re: Grouping by timestamp, how to return 0 when there's no record
| От | Alban Hertroys |
|---|---|
| Тема | Re: Grouping by timestamp, how to return 0 when there's no record |
| Дата | |
| Msg-id | CAF-3MvMQtx9f83zsW9iJqyyvzmOJJF4RHaG_OzPfNPXvWN4uGQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Grouping by timestamp, how to return 0 when there's no record (Koen Van Impe <koen.vanimpe@belnet.be>) |
| Ответы |
Re: Grouping by timestamp, how to return 0 when there's no record
|
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: