在 Postgres，将时间戳缩短为5分钟的最快方法是什么？

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 + 兼容)。

30396 次浏览
小开
最佳答案

我觉得没有更快的方法了。

我觉得你不用担心表情的表现。

执行您的(SELECT，UPDATE，...)语句所涉及的所有其他内容都可能比日期/时间计算开销大得多(例如，检索行的 I/O)。

小开

对于那些想知道(基于@DNS 问题)的完整查询:

假设您有订单，并且希望按照5分钟的切片和 shop _ id 来计算订单:

SELECT date_trunc('hour', created_at) + date_part('minute', created_at)::int / 5 * interval '5 min' AS minute
, shop_id, count(id) as orders_count
FROM orders
GROUP BY 1, shop_id
ORDER BY 1 ASC
小开

我也在想这个问题。我找到了两种可供选择的方法，但是你建议的那种更快。

我非正式地参照了我们的一张大桌子。我将查询限制在前400万行。我在两个查询之间交替使用，以避免由于数据库缓存而使查询获得不公平的优势。

穿越纪元/Unix 时间

SELECT to_timestamp(
floor(EXTRACT(epoch FROM ht.time) / EXTRACT(epoch FROM interval '5 min'))
* EXTRACT(epoch FROM interval '5 min')
) FROM huge_table AS ht LIMIT 4000000

(注意，即使您使用了一个不知道时区的数据类型，也会产生 timestamptz)

结果

  • 运行1 : 39.368秒
  • 运行3 : 39.526秒
  • 运行5 : 39.883秒

使用 date _ trunc 和 date _ part

SELECT
date_trunc('hour', ht.time)
+ date_part('minute', ht.time)::int / 5 * interval '5 min'
FROM huge_table AS ht LIMIT 4000000

结果

  • 运行2 : 34.189秒
  • 运行4 : 37.028秒
  • 运行6 : 32.397秒

系统

  • DB 版本: PostgreSQL 9.6.2 on x86 _ 64-pc-linux-gnu，由 gcc (Ubuntu 4.8.2-19ubuntu1)4.8.2,64位编译
  • 核心: 英特尔至强，E5-1650v2，Hexa-Core
  • 内存: 64 GB，DDR3 ECC 内存

结论

你的版本似乎更快。但对于我的特定用例来说还不够快。不需要指定时间的优点使 epoch 版本更加通用，并且在客户端代码中产生更简单的参量化。它处理 2 hour时间间隔与 5 minute时间间隔一样好，不需要将 date_trunc时间单位参数抬高。最后，我希望将这个时间单位参数改为时间间隔参数。


提交答案