TimescaleDB 2.11

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

select version() "Postgres Version", installed_version "TimescaleDB Version"
from pg_available_extensions
where name = 'timescaledb';

Postgres Version
TimescaleDB Version

PostgreSQL 15.18 (Debian 15.18-1.pgdg11+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2.11.2

SELECT 1

-- a hypertable is TimescaleDB's core idea: one table, partitioned by time
create table reading (t timestamptz not null, sensor int, v double precision);
select create_hypertable('reading','t');

CREATE TABLE

create_hypertable

(1,public,reading,t)

SELECT 1

insert into reading
select '2026-01-01'::timestamptz + (n || ' hour')::interval, 1 + n % 2, n
from generate_series(1,48) n;

INSERT 0 48

-- time_bucket() rolls rows up into fixed windows
select time_bucket('12 hours', t) as bucket, sensor
     , round(avg(v)::numeric,1) as avg_v
from reading group by 1,2 order by 1,2;

bucket
sensor
avg_v

2026-01-01 00:00:00+00
1
6.0

2026-01-01 00:00:00+00
2
6.0

2026-01-01 12:00:00+00
1
17.0

2026-01-01 12:00:00+00
2
18.0

2026-01-02 00:00:00+00
1
29.0

2026-01-02 00:00:00+00
2
30.0

2026-01-02 12:00:00+00
1
41.0

2026-01-02 12:00:00+00
2
42.0

2026-01-03 00:00:00+00
1
48.0

SELECT 9

-- first() and last() pick by time rather than by value
select sensor, first(v,t) as earliest, last(v,t) as latest
from reading group by sensor order by sensor;

sensor
earliest
latest

1
2
48

2
1
47

SELECT 2

bucket	sensor	avg_v
2026-01-01 00:00:00+00	1	6.0
2026-01-01 00:00:00+00	2	6.0
2026-01-01 12:00:00+00	1	17.0
2026-01-01 12:00:00+00	2	18.0
2026-01-02 00:00:00+00	1	29.0
2026-01-02 00:00:00+00	2	30.0
2026-01-02 12:00:00+00	1	41.0
2026-01-02 12:00:00+00	2	42.0
2026-01-03 00:00:00+00	1	48.0