time calculation window function stored procedure

time_window.sql

create function unjustify_hours(interval)
  returns interval
  language sql stable
as $f$
  select $1 - (extract(day from $1) * interval '1 day')
            + (extract(day from $1) * interval '24 hours');
$f$;
comment on function unjustify_hours(interval) is 'inverse of justify_hours';

create or replace function time_within_window(start_ts timestamp,
                                              end_ts timestamp,
                                              window_start time,
                                              window_end time)
  returns interval
  language plpgsql stable strict
as $f$
  declare
    window_offset interval := window_start::interval;
    diff interval;
  begin
    -- we treat our "day" as starting at window_start; adjust window_end to
    -- mark the end of the window according to the new time system

    window_end := window_end - window_offset;

    -- adjust start_ts and end_ts to be relative to our new concept of "day"

    start_ts := start_ts - window_offset;
    end_ts := end_ts - window_offset;

    -- our "day" consists of the live window (00:00 to window_end), followed
    -- by the dead (ignored) window (window_end to 24:00). If either the
    -- start or end times fall in the dead window, clip them to fall exactly
    -- at the end of the live window; this ensures that the period contained
    -- between start_ts and end_ts contains an exact multiple of the dead
    -- window (which we will subtract off at the end)

    start_ts := least(start_ts, start_ts::date + window_end);
    end_ts := least(end_ts, end_ts::date + window_end);

    -- now, the period (start_ts to end_ts) consists of the appropriate
    -- number of hours within the live window(s), plus an integer number of
    -- copies of the entire dead window. The difference in (adjusted) dates
    -- gives the number of copies of the dead window included. We need to
    -- use unjustify_hours here because timestamp subtraction implicitly does
    -- justify_hours, and in this case we don't want that.

    diff := unjustify_hours(end_ts - start_ts);

    -- subtract off the dead window copies

    diff := diff - ( (end_ts::date - start_ts::date)
                     * (time '24:00' - window_end) );

    return diff;
  end;
$f$;