java - 按小时分组的表和平均值

Question

我有一张包含 id、日期、值的表，如何在 postgresql 中获取每小时平均值？

id     date                     value
3470,  2018-11-15 08:10:00+02,  25,101610.0234375
3467,  2018-11-15 07:53:00+02,  33,101398.984375
3468,  2018-11-15 07:54:00+02,  25,101599.765625
3549,  2018-12-28 18:20:00+02,  29.21
3550,  2018-12-28 18:24:00+02,  29.21
3551,  2018-12-28 18:27:00+02,  42.21
3552,  2019-01-07 09:42:00+02,
3553,  2019-01-07 09:50:00+02,  15.140000343323
3554,  2019-01-07 09:52:00+02,  -1.3799999952316
3555,  2019-01-07 10:03:00+02,  14.949999809265

谢谢。

Answer 1

date_trunc('hour', date) 完成这项工作:

demo:db<>fiddle

SELECT
    date_trunc('hour', mydate),
    avg(value)
FROM
    mytable
GROUP BY 1

如果您想保留时区，则需要明确识别它们(date_trunc())消除它们:

SELECT
    date_trunc('hour', mydate at time zone '0') at time zone '+2',
    avg(value)
FROM
    mytable
GROUP BY 1
ORDER BY 1