A template for a PostgreSQL Heroku Dataclip to be viewed in chart mode. Makes it easy to change what you're querying and the time periods.

heroku-chart-dataclip.sql

with vars as (select
  date_trunc('hour', localtimestamp - 
  '24 hours'::interval) as start,      -- how far back to start the chart
  '30 minutes'::interval as bin_width  -- width of time slices
),
data as (
  select
    created_at as "timestamp", -- column to use as timestamp of the record
    1 as "value"               -- use 1 to just count records, or specify a column to aggregate (by sum currently, can be changed below)
    from users                 -- table to query; can add where clause here to restrict results
),
bins as (
  -- Show counts for preceding interval because Heroku dataclip charts show time series as next value, not previous
  select time, tsrange(time - bin_width, time, '(]') as range
  from vars, generate_series(start, localtimestamp, bin_width) time
),
trimmed as (
  -- cutting this table down as a subquery improves performance
  select data.* from vars, data where data.timestamp > start - bin_width
)
select bins.time, sum(value) as "Total"
  from bins
  left outer join trimmed
               on trimmed.timestamp <@ bins.range
group by bins.time
order by bins.time