postgresql date_trunc为任意精度？ [英] postgresql date_trunc to arbitrary precision?

问题描述

postgresql具有 date_trunc ，可以将时间戳记值截断为特定的单位，例如 hour 或 minute 。我想知道是否有任何内置函数可以让我截断到 10分钟？

postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?

我知道一个窍门是转换到时代的时间戳，做一些数学运算，然后再转换回来。

I know one trick is to convert the time stamp to epoch, do some math, then convert back. But I don't like it.

推荐答案

您没有想要的功能，但在 PostgreSQL Wiki 您可以为自己定义函数：

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

通常舍入到$ 2分钟：

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

这篇关于postgresql date_trunc为任意精度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！