CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
SELECT create_hypertable('conditions', 'time');
CREATE INDEX conditions_time_idx
ON public.conditions USING btree
-- additionally partition the data on another
-- dimension (what we call 'space partitioning').
-- E.g., to partition `location` into 4 partitions:
SELECT create_hypertable('conditions', 'time', 'location', 4);
SELECT time_bucket('5 minutes', time) AS time_range,
MAX(temperature) AS max_temp,
WHERE time > NOW() - interval '3 hours'
GROUP BY time_range, location
ORDER BY time_range DESC, max_temp DESC;
time_range | location | count | max_temp | max_hum
------------------------+----------+-------+----------+---------
2018-02-23 17:00:00+00 | office | 3 | 70 | 50
2018-02-23 16:35:00+00 | garage | 1 | 77 | 65.2
2018-02-23 16:35:00+00 | office | 2 | 70.1 | 50.1
2018-02-23 16:35:00+00 | basement | 1 | 66.5 | 60
2018-02-23 16:25:00+00 | office | 1 | 70 | 50