Timescale

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  device      TEXT              NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL

  PRIMARY KEY (time, device, location)
);
SELECT create_hypertable('conditions', 'time'); -- 7 jours
SELECT create_hypertable('conditions', 'time',
  chunk_time_interval => INTERVAL '1 day');
-- si modification du partitionnement plus tard
SELECT set_chunk_time_interval('conditions', INTERVAL '2 days');

CREATE MATERIALIZED VIEW 'conditions_summary_hourly'
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', 'time') AS bucket,
  device,
  location,
  min(temperature) AS min_temperature,
  max(temperature) AS max_temperature,
  max(humidity) - min(humidity) AS humidity_spread
FROM conditions -- doit se baser sur une hypertable
GROUP BY bucket, device, location
WITH NO DATA; -- ne pas populer l'aggrégation à sa création
SELECT set_chunk_time_interval('conditions_summary_hourly',
  INTERVAL '1 day');

SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
  start_offset => INTERVAL '1 month',
  -- recommandé de s'arrêter avant le dernier *bucket*
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- rafraîchir manuellement l'aggrégation (date de fin non comprise)
CALL refresh_continuous_aggregate('conditions_summary_hourly',
  '2024-08-01',
  '2024-09-01');

ALTER MATERIALIZED VIEW 'conditions_summary_hourly'
  SET (timescaledb.materialized_only = false);

-- définition de la durée de rétention
SELECT add_retention_policy('conditions', INTERVAL '6 months')