在Postgres中将时间戳缩短到5分钟的最快方法是什么? [英] What is the fastest way to truncate timestamps to 5 minutes in Postgres?
问题描述
Postgres可以使用date_trunc函数舍入(截断)时间戳,如下所示:
date_trunc('hour',val)
date_trunc('minute',val)
我正在寻找一种截断方式时间戳到最近的5分钟边界,所以例如14:26:57成为14:25:00。直接的方式是这样的:
date_trunc('hour',val)+ date_part('minute',val ):: int / 5 * interval'5 min'
由于这是性能关键部分查询,我想知道这是否是最快的解决方案,还是有一些我忽略的捷径(兼容Postgres 8.1+)。
我不认为有更快的方法。
我不认为你应该担心表情的表现。执行你的(SELECT,UPDATE,...)语句所涉及到的一切都是非常昂贵的(例如检索行的I / O)比日期/时间计算。
Postgres can round (truncate) timestamps using the date_trunc function, like this:
date_trunc('hour', val)
date_trunc('minute', val)
I'm looking for a way to truncate a timestamp to the nearest 5-minute boundary so, for example, 14:26:57 becomes 14:25:00. The straightforward way to do it is like this:
date_trunc('hour', val) + date_part('minute', val)::int / 5 * interval '5 min'
Since this is a performance-critical part of the query, I'm wondering whether this is the fastest solution, or whether there's some shortcut (compatible with Postgres 8.1+) that I've overlooked.
I don't think there is any quicker method.
And I don't think you should be worried about the performance of the expression.
Everything else that is involved in executing your (SELECT, UPDATE, ...) statement is most probably a lot more expensive (e.g. the I/O to retrieve rows) than that date/time calculation.
这篇关于在Postgres中将时间戳缩短到5分钟的最快方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!