将时间戳截短为任意间隔 [英] Truncate timestamp to arbitrary intervals
问题描述
我想在时间轴上比在数据库中具有更低的分辨率来绘制 my_values
。我使用以下SQL来获取每个时间间隔的平均值(例如,一个小时):
I want to plot my_values
with some lower resolution on the time axis than I have in the database. I use something like the following SQL to get the average values per time interval (e.g., an hour):
SELECT DATE_TRUNC('hour', my_timestamps) AS my_time_lowres
, AVG(my_values)
FROM my_table
GROUP BY my_time_lowres
使用 date_trunc()
可以在一定程度上减少时间戳的分辨率(根据文档):
Using date_trunc()
it is possible to reduce the resolution of the timestamp to a certain degree (according to the docs):
select date_trunc('hour', timestamp '2001-02-16 20:38:40')
-- the output is: 2001-02-16 20:00:00
这样,我可以为以下间隔大小(和一些较大/较小的大小):
This way, I can do this for the following interval sizes (and some larger/smaller sizes):
...
second
minute
hour
day
week
...
有没有一种方法来实现这个其他时间int例如3小时6小时?
Is there a way to achieve this for other time intervals as well, e.g., 3 hours, 6 hours?
推荐答案
考虑这个演示将时间戳降低到15分钟并且导致重复的结果:
Consider this demo to bring timestamps down to a resolution of 15 minutes and aggregate resulting dupes:
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纪元。整数除法将所有分辨率的数据块合并在一起,因为小数位数被截断。
The trick is to extract a unix epoch like @Michael already posted. Integer division lumps them together in buckets of the chosen resolution, because fractional digits are truncated.
除以900,因为15分钟= 900秒。
I divide by 900, because 15 minutes = 900 seconds.
乘以相同的数字,得到最终的 lower_bound
。
使用 将unix纪元转换回时间戳code> to_timestamp()
Multiply by the same number to get the resulting lower_bound
.
Convert the unix epoch back to a timestamp with to_timestamp()
.
这对于没有分数十进制数字。对于更多的多功能性,使用经常被忽视的功能 width_bucket()
,就像我在这个最近的密切相关的答案。更多解释,链接和sqlfiddle演示。
This works great for intervals that can be represented without fractional digits in the decimal system. For even more versatility use the often overlooked function width_bucket()
like I demonstrate in this recent, closely related answer. More explanation, links and an sqlfiddle demo over there.
这篇关于将时间戳截短为任意间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!