sql - 从 TimescaleDB 获取 1 年的结果

Question

我有一张包含物联网设备数据的表格，每小时发送一次测量值。必须获取今天、每周、每月和每年的报告。

使用 timescaleDB，我得到了今天、每周和每月的一些“好的”结果。例如每月:

   SELECT
      time_bucket('4 weeks', measured_at) AS month,
      MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
      FROM readings
      WHERE device_id = 4
      GROUP BY month
      ORDER BY month DESC LIMIT 12;

但多年来一直找不到好的取值方式？有人试过吗？

时间刻度不支持年，使用周会导致错误结果。

错误:不支持按月、年、世纪等定义的间隔

Answer 1

请确保您知道您实际聚合的是哪些数据 time_bucket :

TIMESTAMPTZ arguments are bucketed by the time at UTC. So the alignment of buckets is on UTC time. One consequence of this is that daily buckets are aligned to midnight UTC, not local time.

正如@TmTron 已经指出的，几个月和几年的实际正确版本是使用 date_trunc，如下所示:

SELECT
    date_trunc('month', measured_at) AS month,
    MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
    FROM readings
    WHERE device_id = 4
    GROUP BY 1
    ORDER BY 1 DESC LIMIT 12;

...和:

SELECT
    date_trunc('year', measured_at) AS year,
    MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
    FROM readings
    WHERE device_id = 4
    GROUP BY 1
    ORDER BY 1 DESC LIMIT ...;

如果您只选择某个时间间隔(例如过去 12 个月)，请始终添加条件以减少要扫描的分区数量，例如:

SELECT
    date_trunc('month', measured_at) AS month,
    MAX(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) - MIN(CAST(data->>'1_8_0' AS DOUBLE PRECISION)) as consumption
    FROM readings
    WHERE device_id = 4
        AND measured_at >= CURRENT_TIMESTAMP - '13 months'::interval
    GROUP BY 1
    ORDER BY 1 DESC LIMIT 12;