Skip to content

Instantly share code, notes, and snippets.

Last active May 1, 2020 11:35
What would you like to do?
A stored PostgreSQL function to generate time range series. Join your data to the function's result and group by date_from in order to build a time-series histogram without gaps even if there is no data in some time ranges.
CREATE OR REPLACE FUNCTION no_gaps_time_ranges(
in start_date timestamptz,
in interval_len_sec bigint,
in num_intervals bigint
RETURNS TABLE (date_from timestamptz, date_to timestamptz)
LANGUAGE plpgsql
AS $function$
abs_interval_len_sec bigint;
seconds_start bigint;
seconds_start_boundary bigint;
series_from bigint;
series_to bigint;
abs_interval_len_sec = abs(interval_len_sec);
select trunc(extract(epoch from start_date))::bigint into seconds_start;
-- raise notice 'seconds_start = %', seconds_start;
select seconds_start - mod(seconds_start,interval_len_sec) into seconds_start_boundary;
-- raise notice 'seconds_start_boundary = %', seconds_start_boundary;
if interval_len_sec < 0
then -- going backwards
series_from = seconds_start_boundary + num_intervals * interval_len_sec;
series_to = seconds_start_boundary + interval_len_sec;
else -- going forward
series_from = seconds_start_boundary;
series_to = seconds_start_boundary + (num_intervals-1) * interval_len_sec;
end if;
--raise notice 'series: from = %, to = %', series_from, series_to;
return query
with sec_thresholds as (
select generate_series(series_from, series_to, abs_interval_len_sec) as sec_thr
to_timestamp(sec_thr) as date_from,
to_timestamp(sec_thr + abs_interval_len_sec) as date_to
from sec_thresholds;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment