trim_to now for heartbeat aggregations

[effnish]

I'm struggling with an edge-case with the heartbeat aggregation that I can't find an ergonomic solution to. I have continuous aggregates on a heartbeat table and I've expended a lot of effort getting an accurate uptime percentage for the current time interval. I could omit that interval, but it's arguably the most important interval. If the system is down I want to know... now.

For example, let's assume I have an hourly continuous aggregation and now() == 08:10:00

• uptime(agg) reports 10 mins of uptime, check
• downtime(agg) reports 50 mins of downtime, the crux of the issue
• My uptime percentage uptime(agg)/(uptime(agg) + downtime(agg) falsely reports 16.67%

I've been working around the problem by calculating the valid duration on the last time bucket with something like:

SELECT
    bucket_hour,
    uptime(agg) AS uptime,
    CASE
        WHEN bucket_hour = date_trunc('hour', now())
            THEN now() - date_trunc('hour', now()) - '1m'::INTERVAL
        ELSE
            uptime(agg) + downtime(agg)
    END AS total_time
FROM hourly_heartbeats ;

It would be very helpful if I could trim a heartbeat aggregate to now or supply a cut-off period for the downtime, interpolated_downtime and dead_ranges functions.

Something like:

SELECT
    bucket_hour,
    downtime(trim_to(agg, 'now', INTERVAL '1m'))
FROM hourly_heartbeats;