postgresql Postgres：如何将时间戳向上或向下舍入到最接近的分钟？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6195439/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: how do you round a timestamp up or down to the nearest minute?
提问by dan
Is there a postgresql function that will return a timestamp rounded to the nearest minute? The input value is a timestamp and the return value should be a timestamp.
是否有一个 postgresql 函数会返回一个四舍五入到最接近分钟的时间戳？输入值是一个时间戳，返回值应该是一个时间戳。
回答by Bohemian
Use the built-in function date_trunc(text, timestamp), for example:
使用内置函数date_trunc(text, timestamp)，例如：
select date_trunc('minute', now())
Edit:This truncates to the most recentminute. To get a roundedresult, add 30 seconds to the timestamp first, for example:
编辑：这会截断到最近一分钟。要获得四舍五入的结果，请先向时间戳添加 30 秒，例如：
select date_trunc('minute', now() + interval '30 second')
This returns the nearestminute.
这将返回最近的分钟。
See Postgres Date/Time Functions and Operatorsfor more info
有关详细信息，请参阅Postgres 日期/时间函数和运算符
回答by Peter Krauss
Answer to a similar (and more generic) question,
回答一个类似（更通用）的问题，
"... to the nearest minute interval" (1-minute, 5-minutes, 10-minutes, etc.)
“... 到最近的分钟间隔”（1 分钟、5 分钟、10 分钟等）
CREATE FUNCTION round_minutes(TIMESTAMP WITHOUT TIME ZONE, integer)
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$
SELECT
date_trunc('hour', )
+ cast((::varchar||' min') as interval)
* round(
(date_part('minute',)::float + date_part('second',)/ 60.)::float
/ ::float
)
$$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION round_minutes(TIMESTAMP WITHOUT TIME ZONE, integer,text)
RETURNS text AS $$
SELECT to_char(round_minutes(,),)
$$ LANGUAGE SQL IMMUTABLE;
SELECT round_minutes('2010-09-17 16:23:12', 5);
-- 2010-09-17 16:25:00
SELECT round_minutes('2010-09-17 16:23:12', 10, 'HH24:MI');
-- 16:20
Adapted from http://wiki.postgresql.org/wiki/Round_timeand to the "exact round" as @CrowMagnumb showed.
改编自 http://wiki.postgresql.org/wiki/Round_time和@CrowMagnumb 所示的“精确回合”。
回答by crowmagnumb
In trying to use Peter's answer above to create ceiling and floor functions I found that you have to take into account the seconds too when calling the rounding function. This here are my sets of functions that will round, floor, and ceiling timestamps.
在尝试使用上面彼得的答案来创建天花板和地板函数时，我发现在调用舍入函数时也必须考虑秒数。这是我的一组函数，它们将舍入、下限和上限时间戳。
CREATE OR REPLACE FUNCTION round_minutes( TIMESTAMP WITHOUT TIME ZONE, integer)
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$
SELECT date_trunc('hour', ) + (cast((::varchar||' min') as interval) * round( (date_part('minute',)::float + date_part('second',)/ 60.)::float / cast( as float)))
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION floor_minutes( TIMESTAMP WITHOUT TIME ZONE, integer )
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$
SELECT round_minutes( - cast(((/2)::varchar ||' min') as interval ), );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION ceiling_minutes( TIMESTAMP WITHOUT TIME ZONE, integer )
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$
SELECT round_minutes( + cast(((/2)::varchar ||' min') as interval ), );
$$ LANGUAGE SQL IMMUTABLE STRICT;
回答by James De Souza
to round down a timestamp
舍入时间戳
CREATE or replace FUNCTION date_round_down(base_date timestamptz, round_interval INTERVAL)
RETURNS timestamptz AS $BODY$
SELECT TO_TIMESTAMP(EXTRACT(epoch FROM date_trunc('hour', ))::INTEGER + trunc((EXTRACT(epoch FROM )::INTEGER - EXTRACT(epoch FROM date_trunc('hour', ))::INTEGER) / EXTRACT(epoch FROM )::INTEGER) * EXTRACT(epoch FROM )::INTEGER)
$BODY$ LANGUAGE SQL STABLE;
SELECT date_round_down('2017-06-02 16:39:35', '15 minutes') -- 2017-06-02 16:30:35
