gpt4 book ai didi

sql - 将时间戳截断为任意间隔

转载 作者:行者123 更新时间:2023-11-29 11:40:51 25 4
gpt4 key购买 nike

我想绘制my_values,时间轴上的分辨率比我在数据库中的分辨率低一些。我使用类似于以下 SQL 的东西来获取每个时间间隔(例如,一个小时)的平均值:

SELECT DATE_TRUNC('hour', my_timestamps) AS my_time_lowres
, AVG(my_values)
FROM my_table
GROUP BY my_time_lowres

使用 date_trunc() 可以在一定程度上降低时间戳的分辨率(根据 the docs ):

select date_trunc('hour', timestamp '2001-02-16 20:38:40') 
-- the output is: 2001-02-16 20:00:00

这样,我可以针对以下间隔大小(以及一些更大/更小的大小)执行此操作:

...
second
minute
hour
day
week
...

有没有办法在其他时间间隔也能实现这一点,例如 3 小时、6 小时?

最佳答案

考虑这个演示,将时间戳降低到 15 分钟的分辨率,并聚合生成的重复:

WITH tbl(id, ts) AS ( VALUES
(1::int, '2012-10-04 00:00:00'::timestamp)
,(2, '2012-10-04 18:23:01')
,(3, '2012-10-04 18:30:00')
,(4, '2012-10-04 18:52:33')
,(5, '2012-10-04 18:55:01')
,(6, '2012-10-04 18:59:59')
,(7, '2012-10-05 11:01:01')
)
SELECT to_timestamp((extract(epoch FROM ts)::bigint / 900)*900)::timestamp
AS lower_bound
, to_timestamp(avg(extract(epoch FROM ts)))::timestamp AS avg_ts
, count(*) AS ct
FROM tbl
GROUP BY 1
ORDER BY 1;

结果:

     lower_bound     |       avg_ts        | ct
---------------------+---------------------+----
2012-10-04 00:00:00 | 2012-10-04 00:00:00 | 1
2012-10-04 18:15:00 | 2012-10-04 18:23:01 | 1
2012-10-04 18:30:00 | 2012-10-04 18:30:00 | 1
2012-10-04 18:45:00 | 2012-10-04 18:55:51 | 3
2012-10-05 11:00:00 | 2012-10-05 11:01:01 | 1

诀窍是提取一个 unix 时代,就像@Michael 已经发布的那样。整数除法将它们集中在所选分辨率的桶中,因为小数位被截断了。

我除以 900,因为 15 分钟 = 900 秒。

乘以相同的数字得到结果 lower_bound。使用 to_timestamp() 将 unix 纪元转换回时间戳.

这对于可以在十进制系统中不用小数位表示的区间非常有用。要获得更多功能,请使用经常被忽视的功能 width_bucket()就像我在这个 recent, closely related answer 中展示的那样.更多解释、链接和 sqlfiddle 演示。

关于sql - 将时间戳截断为任意间隔,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12730072/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com