date_trunc PostgreSQL中的5分钟间隔 [英] date_trunc 5 minute interval in PostgreSQL
问题描述
可能重复:
Postgresql SQL GROUP BY具有任意精度的时间间隔(下限为毫秒)
我想以5分钟的时间间隔汇总数据在PostgreSQL中。如果我使用 date_trunc()
函数,我可以按小时,每月,每天,每周等间隔汇总数据,但不能像5分钟或5天。
从table1 group 1中选择date_trunc('hour',date1),count(*);有没有人知道如何在PostgreSQL中实现这一点?
$ b $ / code $ $ b 任何帮助都不胜感激。
解决方案
SELECT date_trunc('hour' ,date1)AS hour_stump
,(extract(minute FROM date1):: int / 5)AS min5_slot
,count(*)
FROM table1
GROUP BY 1,2
订单1,2;
您可以 GROUP BY
两列:a时间戳被截断为小时和5分钟。
该示例生成插槽 0 - 11
。添加 1
如果你喜欢 1 - 12
。
我投下了一个 extract()
进行整数,所以分割 / 5
截断小数位数。结果:
分钟0 - 4 - >插槽0
分钟5 - 9 - >插槽1
等。
此查询仅返回找到值的5分钟插槽的值。如果您想要每个插槽的值,或者如果您想要超过5分钟插槽的运行总和,请考虑以下相关答案:
PostgreSQL:运行查询的行数分钟'
Possible Duplicate:
What is the fastest way to truncate timestamps to 5 minutes in Postgres?
Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)
I want to aggregate data at a 5 minute interval in PostgreSQL. If I use the date_trunc()
function, I can aggregate data at an hourly, monthly, daily, weekly, etc. interval but not a specific interval like 5 minute or 5 days.
select date_trunc('hour', date1), count(*) from table1 group by 1;
Does anyone know how we can achieve this in PostgreSQL?
Any help is appreciated.
解决方案 SELECT date_trunc('hour', date1) AS hour_stump
,(extract(minute FROM date1)::int / 5) AS min5_slot
,count(*)
FROM table1
GROUP BY 1, 2
ORDER BY 1, 2;
You could GROUP BY
two columns: a timestamp truncated to the hour and a 5-minute-slot.
The example produces slots 0 - 11
. Add 1
if you prefer 1 - 12
.
I cast the result of extract()
to integer, so the division / 5
truncates fractional digits. The result:
minute 0 - 4 -> slot 0
minute 5 - 9 -> slot 1
etc.
This query only returns values for those 5-minute slots where values are found. If you want a value for every slot or if you want a running sum over 5-minute slots, consider this related answer:
PostgreSQL: running count of rows for a query 'by minute'
这篇关于date_trunc PostgreSQL中的5分钟间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!