Re: Grouping by timestamp, how to return 0 when there's no record - Mailing list pgsql-general

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!