postgresql - 如果 db 中的数据具有时区偏移量，则按天 + pair_id 分组总和

Question

我有一个服务器，它给我一些按天分组的信息（我用date_trunc()它）。由于时区，我知道2020-06-06 21:00:00在我的数据库中实际上是2020-06-07 00:00:00. 我需要计算列元的总和，按 分组(tm + pair_id)，但由于来自具有2020-06-06 21:00tm 的 db 的偏移数据不与其他2020-06-07数据相加。结果，而不是总金额，day+pair_id我有几行相同的tm和pair_id（因为2020-06-06 21:00在2020-06-07 00:00结果中）

我可以在这里修复分组而不创建子请求吗？

我有表：

   CREATE TABLE tests.tbl (
    id int4 NULL,
    tm timestamp NULL,
    meta float4 NULL,
    pair_id int4 NULL
);

有了这些数据：

我的查询是：

SELECT 
    pair_id,
    date_trunc('day', min(tm) at time zone '3' at time zone 'UTC') as tm,
    sum(meta)
FROM tests.tbl
WHERE (tm BETWEEN '2020-06-06 21:00:00+00:00:00' AND '2020-06-08 20:59:00+00:00:00')
group by pair_id, tm
order by tm, pair_id;

我的结果是：

预期结果是：

标签： postgresqldategroup-bytimestamptimezone

Answer 1

我认为你想要的逻辑是：

SELECT 
    pair_id,
    date_trunc('day', tm at time zone '3' at time zone 'UTC') as tm,
    sum(meta)
FROM tests.tbl
WHERE tm BETWEEN '2020-06-06 21:00:00+00:00:00' AND '2020-06-08 21:00:00+00:00:00'
GROUP BY 1, 2
ORDER BY 2, 1;

即：selectandgroup by子句要保持一致（特别是两个子句中的timestamp 列要偏移）。

如果将偏移量移动到子查询中，可能会更容易理解：

SELECT pair_id, date_trunc('day', tm) as tm, sum(meta)
FROM (
    SELECT 
        pair_id,
        tm at time zone '3' at time zone 'UTC' as tm,
        meta
    FROM tests.tbl
) t
WHERE tm >= '2020-06-07'::date AND tm < '2020-06-09'::date
GROUP BY 1, 2
ORDER BY 2, 1;