Skip to content

Instantly share code, notes, and snippets.

@jonahgeorge
Created October 3, 2017 13:30
Show Gist options
  • Save jonahgeorge/f5bef56419a5e67cf3e5583346ddd9a1 to your computer and use it in GitHub Desktop.
Save jonahgeorge/f5bef56419a5e67cf3e5583346ddd9a1 to your computer and use it in GitHub Desktop.
Weatherglass Bucket Query
create or replace function bucket(timestamp, interval)
returns timestamptz
as $$
select
to_timestamp(
floor(
extract(epoch from $1) /
extract(epoch from $2)
) * extract(epoch from $2)
);
$$ language sql;
with timeslices as (
select
interval,
0 as blank_count
from generate_series(
bucket($2, $4),
bucket($3, $4),
$4::interval
) as interval
),
events_per_interval as (
select
bucket(created_at, $4) as interval,
count(*) as count
from events
where site_id = $1
and created_at >= $2
group by interval
)
select
timeslices.interval as interval,
coalesce(events_per_interval.count, timeslices.blank_count) as count
from timeslices
left outer join events_per_interval
on events_per_interval.interval = timeslices.interval
order by timeslices.interval;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment