postgresql - TimescaleDB / PostgreSQL 每日最大时区偏移量

Question

我在使用时区偏移对数据进行排序和查询时遇到问题。

我有 PostgreSQL + TimescaleDB，我有时间序列数据。目标是根据当地时区获得每日最大值。我的数据是 UTC，但查询需要在本地时区，因为我的累积计数器在本地时间重置。

目标是有如下结果

  id   |    time    |      dailymax    |        name
-------+---------------------+------------------+------------------------
 10001 | 2020-01-05 |                0 | Property1
 10002 | 2020-01-05 | 20930.3490579984 | Property2
 10003 | 2020-01-05 | 28347.1322223556 | Property3
 10001 | 2020-01-04 | 57872.3274949118 | Property1
 10002 | 2020-01-04 | 101159.612050399 | Property2
 10003 | 2020-01-04 |  34113.226521315 | Property3
 10001 | 2020-01-03 |                0 | Property1
 10002 | 2020-01-03 | 17386.2914203308 | Property2
 10003 | 2020-01-03 | 160599.774657208 | Property3

到目前为止，我最好的猜测是下面的查询，但它也给了我来自错误日期的记录并且需要很长时间才能执行

SELECT table.id, CAST(datetime AS DATE) AT TIME ZONE 'America/New_York' as time, MAX(value) AS value, map.tagname FROM table
                      JOIN map ON table.id = map.id
                        WHERE table.id in(10003,10029,10008,10036,10007) AND datetime AT TIME ZONE 'UTC' >= '2019-12-30T16:09:32.080Z' AND datetime AT TIME ZONE 'UTC' < '2020-01-06T16:09:32.080Z'
                    GROUP BY table.id, map.tagname, time
                    ORDER BY time DESC;

使用 TimescaleDB 也尝试了如下所示的方法，但运气不好

SELECT table.id, to_char(time_bucket('24 hours', datetime) at time zone 'utc', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as time, max(value) as value, map.name FROM
    table JOIN map ON table.id = map.id
    WHERE table.id in(10001,10002) AND datetime >= '2020-01-01' AND datetime < '2020-01-10'
GROUP BY time, table.id, map.name ORDER BY time DESC`;

标签： postgresqlgroup-bytimescaledb