I’m working on a project for server uptime.
I’ve tried using duration_in and interpolated_duration_in, but I think I need some help getting that going.
Here are some SQL commands to set up tables and continuous aggregates:
DROP MATERIALIZED VIEW IF EXISTS connect_daily CASCADE;
DROP MATERIALIZED VIEW IF EXISTS connect_hourly CASCADE;
DROP TABLE IF EXISTS connect_events CASCADE;
CREATE TABLE IF NOT EXISTS connect_events(server_id integer NOT NULL, status text NOT NULL, occurred_at timestamptz NOT NULL);
CREATE UNIQUE INDEX idx_connet_events ON connect_events (server_id, occurred_at);
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit;
SELECT * from create_hypertable('connect_events', 'occurred_at');
CREATE MATERIALIZED VIEW connect_hourly
WITH (timescaledb.continuous) AS
SELECT server_id, time_bucket(INTERVAL '1 hour', occurred_at) AS hourly_bucket, state_agg(occurred_at, status)
FROM connect_events
GROUP BY server_id, hourly_bucket
ORDER BY server_id, hourly_bucket
WITH DATA;
SELECT remove_continuous_aggregate_policy('connect_hourly');
SELECT add_continuous_aggregate_policy('connect_hourly',
start_offset => INTERVAL '1 day',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
CREATE MATERIALIZED VIEW connect_daily
WITH (timescaledb.continuous)
AS SELECT
time_bucket('1 day', hourly_bucket) as daily_bucket,
server_id,
rollup(cah.state_agg) as daily
FROM connect_hourly cah
GROUP BY daily_bucket, server_id
ORDER BY daily_bucket, server_id
WITH DATA;
SELECT remove_continuous_aggregate_policy('connect_daily');
SELECT add_continuous_aggregate_policy('connect_daily',
start_offset => INTERVAL '1 month',
end_offset => INTERVAL '1 day',
schedule_interval => INTERVAL '1 day');
delete from connect_events;
insert into connect_events (server_id, status, occurred_at) values(1, 'connected', '2023-08-01 00:00:00');
insert into connect_events (server_id, status, occurred_at) values(1, 'disconnected', '2023-08-22 12:00:00');
insert into connect_events (server_id, status, occurred_at) values(1, 'connected', '2023-08-22 12:05:00');
insert into connect_events (server_id, status, occurred_at) values(1, 'disconnected', '2023-08-23 09:00:00');
insert into connect_events (server_id, status, occurred_at) values(1, 'connected', '2023-08-23 09:10:00');
insert into connect_events (server_id, status, occurred_at) values(1, 'disconnected', '2023-08-24 12:00:00');
insert into connect_events (server_id, status, occurred_at) values(1, 'connected', '2023-08-24 14:00:00');
select * from connect_events;
CALL refresh_continuous_aggregate('connect_hourly', '2023-01-01', '2024-01-01');
CALL refresh_continuous_aggregate('connect_daily', '2023-01-01', '2024-01-01');
select * from connect_hourly order by server_id, hourly_bucket;
SELECT * FROM connect_hourly ORDER BY hourly_bucket;
SELECT * FROM connect_daily ORDER BY daily_bucket;
SELECT
date(daily_bucket),
interpolated_duration_in(
daily,
'connected',
daily_bucket,
'1 day',
LAG(daily) OVER (ORDER BY daily_bucket)
) as connected,
interpolated_duration_in(
daily,
'disconnected',
daily_bucket,
'1 day',
LAG(daily) OVER (ORDER BY daily_bucket)
) as disconnected
FROM connect_daily
WHERE server_id = 1
AND daily_bucket >= '2023-08-22 00:00:00'
AND daily_bucket < '2023-08-23 00:00:00';
When I run the interpolated_duration_in query for Aug 22, I get 10 minutes of disconnected time, 7:50 of connected time.
The disconnected time is correct, but I’m expecting to see a total of 24 hours, and 23:50 of connected time.
When I run it for the 23rd, I get 3 hours connected, 2 hours disconnected.
What am I doing wrong?
