TimescaleDB 2.11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select installed_version from pg_available_extensions where name = 'timescaledb';


installed_version


2.11.1



SELECT 1


select setseed(0.2);

create table public.values(variableid int,IntValue int,created_at timestamptz);

SELECT create_hypertable('values', 'created_at');

/*
--generate more test data
insert into public.values 
select generate_series(1,3,1),
       random()*10,
       now()-'10 minutes'::interval*random()
from generate_series(1,5,1)
returning *;
*/
insert into public.values values
(1,1,'2023-09-27 12:40'),
(1,2,'2023-09-27 12:41'),
(1,2,'2023-09-27 12:41'),
(1,3,'2023-09-27 12:42'),
(1,3,'2023-09-27 12:42'),
(1,3,'2023-09-27 12:42'),
(1,4,'2023-09-27 12:43'),
(1,4,'2023-09-27 12:43'),
(1,4,'2023-09-27 12:43'),
(1,4,'2023-09-27 12:43'),
(1,5,'2023-09-27 12:44'),
(1,5,'2023-09-27 12:44'),
(1,5,'2023-09-27 12:44'),
(1,5,'2023-09-27 12:44'),
(1,5,'2023-09-27 12:44')
returning *;


variableid
intvalue
created_at


1
1
2023-09-27 12:40:00+00

1
2
2023-09-27 12:41:00+00

1
2
2023-09-27 12:41:00+00

1
3
2023-09-27 12:42:00+00

1
3
2023-09-27 12:42:00+00

1
3
2023-09-27 12:42:00+00

1
4
2023-09-27 12:43:00+00

1
4
2023-09-27 12:43:00+00

1
4
2023-09-27 12:43:00+00

1
4
2023-09-27 12:43:00+00

1
5
2023-09-27 12:44:00+00

1
5
2023-09-27 12:44:00+00

1
5
2023-09-27 12:44:00+00

1
5
2023-09-27 12:44:00+00

1
5
2023-09-27 12:44:00+00



INSERT 0 15


CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_one_minute_1
WITH (timescaledb.continuous,timescaledb.materialized_only = true) AS
SELECT  variableid, 
        time_bucket(INTERVAL '1 minute', created_at) AS bucket_interval_one_min,
        MIN(IntValue) as Min_IntValue, 
        MAX(IntValue) as Max_IntValue, 
        SUM(IntValue) as Sum_IntValue,
        COUNT(IntValue) as Count_IntValue, 
        stats_agg(IntValue) as statsagg_IntValue
FROM public.values
GROUP BY variableid, bucket_interval_one_min;

CREATE MATERIALIZED VIEW


select * from public.values_summary_one_minute_1;


variableid
bucket_interval_one_min
min_intvalue
max_intvalue
sum_intvalue
count_intvalue
statsagg_intvalue


1
2023-09-27 12:40:00+00
1
1
1
1
(version:1,n:1,sx:1,sx2:0,sx3:0,sx4:0)

1
2023-09-27 12:41:00+00
2
2
4
2
(version:1,n:2,sx:4,sx2:0,sx3:0,sx4:0)

1
2023-09-27 12:42:00+00
3
3
9
3
(version:1,n:3,sx:9,sx2:0,sx3:0,sx4:0)

1
2023-09-27 12:43:00+00
4
4
16
4
(version:1,n:4,sx:16,sx2:0,sx3:0,sx4:0)

1
2023-09-27 12:44:00+00
5
5
25
5
(version:1,n:5,sx:25,sx2:0,sx3:0,sx4:0)



SELECT 5


CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_five_minutes
WITH (timescaledb.continuous,timescaledb.materialized_only = true) AS
SELECT  variableid, 
        time_bucket(INTERVAL '5 minute', bucket_interval_one_min) AS bucket_interval_five_min,
        MIN(Min_IntValue) as Min_IntValue, 
        MAX(Max_IntValue) as Max_IntValue, 
        SUM(Sum_IntValue) as Sum_IntValue,
        COUNT(Count_IntValue) as Count_IntValue, 
        rollup(statsagg_IntValue) as Stats_IntValue, 
        average(rollup(statsagg_IntValue)) as Avg_IntValue,     --this
        avg(average(statsagg_IntValue)) as Avg_1minAvg_IntValue 
FROM public.values_summary_one_minute_1
GROUP BY variableid, bucket_interval_five_min

CREATE MATERIALIZED VIEW


select variableid, 
       bucket_interval_five_min,
       Min_IntValue,
       Max_IntValue,
       Sum_IntValue,
       Count_IntValue,
       Avg_IntValue,
       Avg_1minAvg_IntValue 
from public.values_summary_five_minutes;


variableid
bucket_interval_five_min
min_intvalue
max_intvalue
sum_intvalue
count_intvalue
avg_intvalue
avg_1minavg_intvalue


1
2023-09-27 12:40:00+00
1
5
55
5
3.6666666666666665
3



SELECT 1

variableid	intvalue	created_at
1	1	2023-09-27 12:40:00+00
1	2	2023-09-27 12:41:00+00
1	2	2023-09-27 12:41:00+00
1	3	2023-09-27 12:42:00+00
1	3	2023-09-27 12:42:00+00
1	3	2023-09-27 12:42:00+00
1	4	2023-09-27 12:43:00+00
1	4	2023-09-27 12:43:00+00
1	4	2023-09-27 12:43:00+00
1	4	2023-09-27 12:43:00+00
1	5	2023-09-27 12:44:00+00
1	5	2023-09-27 12:44:00+00
1	5	2023-09-27 12:44:00+00
1	5	2023-09-27 12:44:00+00
1	5	2023-09-27 12:44:00+00

variableid	bucket_interval_one_min	min_intvalue	max_intvalue	sum_intvalue	count_intvalue	statsagg_intvalue
1	2023-09-27 12:40:00+00	1	1	1	1	(version:1,n:1,sx:1,sx2:0,sx3:0,sx4:0)
1	2023-09-27 12:41:00+00	2	2	4	2	(version:1,n:2,sx:4,sx2:0,sx3:0,sx4:0)
1	2023-09-27 12:42:00+00	3	3	9	3	(version:1,n:3,sx:9,sx2:0,sx3:0,sx4:0)
1	2023-09-27 12:43:00+00	4	4	16	4	(version:1,n:4,sx:16,sx2:0,sx3:0,sx4:0)
1	2023-09-27 12:44:00+00	5	5	25	5	(version:1,n:5,sx:25,sx2:0,sx3:0,sx4:0)