How to Use generate_series to Avoid Gaps In Data in PostgreSQL

If you're grouping by time and you don't want any gaps in your data, PostgreSQL's generate_series can help. The function wants three arguments: start , stop , and interval :

select generate_series(
  date_trunc('hour', now()) - '1 day'::interval, -- start at one day ago, rounded to the hour
  date_trunc('hour', now()), -- stop at now, rounded to the hour
  '1 hour'::interval -- one hour intervals
) as hour

          hour
------------------------
 2017-12-22 13:00:00-08
 2017-12-22 14:00:00-08
 2017-12-22 15:00:00-08
 2017-12-22 16:00:00-08
 2017-12-22 17:00:00-08
 ...

Now you can use a common table expression to create a table that has a row for each interval (ie each hour of the day), and then left join that with your time series data (ie new user sign ups per hour).

with hours as (
  select generate_series(
    date_trunc('hour', now()) - '1 day'::interval,
    date_trunc('hour', now()),
    '1 hour'::interval
  ) as hour