时间戳

首页 > 解决方案 > 平均时间戳数据在 postgres 中给出错误

sql - 平均时间戳数据在 postgres 中给出错误

问题描述

一段时间以来，我一直在努力寻找答案。让我尝试迭代问题。我有一个名为 myTable 的表：（注意：下表只是具有数千行的大表的一小部分）

timestamp_original            data
2020/05/24 15.30.31.620         0
2020/05/24 15.30.31.649         1
2020/05/24 15.30.31.658         2
2020/05/24 15.30.31.668         3
2020/05/24 15.30.31.688         4
2020/05/24 15.30.31.698         5
2020/05/24 15.30.31.708         6
2020/05/24 15.30.31.738         7
2020/05/24 15.30.31.748         8
2020/05/24 15.30.31.758         9
2020/05/24 15.30.31.768         10
2020/05/24 15.30.31.809         11
2020/05/24 15.30.31.810         12
2020/05/24 15.30.31.812         13
2020/05/24 15.30.31.838         14
2020/05/24 15.30.31.848         15

我的期望是获得每毫秒之间的数据平均值，同时将毫秒舍入到 10 秒。

timestamp_new                  myData
2020/05/24 15.30.31.6           2.5
2020/05/24 15.30.31.7           8
2020/05/24 15.30.31.8           13

我的代码如下：

SELECT date_trunc('milliseconds','timestamp_original')::timestamp(1) as timestamp_new, 
avg(data) as myData
FROM myTable
GROUP BY 1 ORDER BY timestamp_new;

但我得到的输出如下，这没有任何意义

timestamp_new                  myData
2020/05/24 15.30.31.6           0.5
2020/05/24 15.30.31.7           5
2020/05/24 15.30.31.8           12

请帮我。

标签： sqlpostgresqltime-series

解决方案

转换为timestamp(1)舍入而不是截断。一个简单的解决方案是减去 50 毫秒：

SELECT date_trunc('millisecond', timestamp_original - interval '50 millisecond')::timestamp(1) as timestamp_new,
       avg(data) as myData
FROM myTable
GROUP BY 1
ORDER BY timestamp_new;

这是一个 db<>fiddle。

推荐阅读