时间取整

库代码片段

将时间戳四舍五入到最接近的 5 分钟标记

适用于 PostgreSQL

8.2

编写语言

PL/pgSQL

依赖项

无

将时间戳四舍五入到最接近的 5 分钟标记。

CREATE OR REPLACE FUNCTION round_time(timestamp with time zone) 
RETURNS timestamp with time zone AS $$ 
  SELECT date_trunc('hour', $1) + interval '5 min' * round(date_part('minute', $1) / 5.0) 
$$ LANGUAGE sql;

示例用法

postgres=# select now(), round_time('2010-09-17 16:48');
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 08:36:31.701919+02 | 2010-09-17 16:50:00+02
(1 row)

postgres=# select now(), round_time('2010-09-17 16:58');
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 08:36:43.860858+02 | 2010-09-17 17:00:00+02
(1 row)

postgres=# select now(), round_time('2010-09-17 16:57');
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 08:36:53.273612+02 | 2010-09-17 16:55:00+02
(1 row)

postgres=# select now(), round_time('2010-09-17 23:58');
             now              |       round_time       
------------------------------+------------------------
 2010-09-19 08:37:09.41387+02 | 2010-09-18 00:00:00+02
(1 row)

备用方法

CREATE FUNCTION date_round(base_date timestamptz, round_interval interval) RETURNS timestamptz AS $BODY$
SELECT to_timestamp((EXTRACT(epoch FROM $1)::integer + EXTRACT(epoch FROM $2)::integer / 2)
                / EXTRACT(epoch FROM $2)::integer * EXTRACT(epoch FROM $2)::integer)
$BODY$ LANGUAGE SQL STABLE;

这也可以跨越一个小时的边界，例如四舍五入到最接近的一个半小时。示例用法

SELECT date_round(now(), '15 minutes');