如何将时间四舍五入到任意时间间隔的上倍数？

假设数据类型timestamp. date或的某些细节不同timestamptz。

任何时间间隔的通用解决方案都可以根据纪元值和整数除法进行截断。涵盖了您的所有示例。

你的任务的特殊困难：你想要天花板，而不是地板（这更常见）。小心上下限以避免极端情况错误：您不想增加确切的底值。（或者我假设。）

对于内置的常见时间间隔date_trunc()（例如1 hour和1 day您的示例），您可以使用快捷方式。天数的定义取决于会话的时区设置 with timestamptz（但不是 with timestamp）。

一个自然的选择是 with ceil()。在我的测试中有点慢，但更干净。

简短的演示

-- short demo
WITH t(ts) AS (SELECT timestamp '2018-05-17 22:45:30')  -- your input timestamp
SELECT t2.*
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1 -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min' , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min', to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'  , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'   , date_trunc('day' , ts1) + interval '1 day')
   ) t2(interval, ceil_ts);

间隔 | ceil_ts            
:---------------- | :-----------------
输入时间戳 | 2018-05-17 22:45:30
5 分钟 | 2018-05-17 22:50:00
10 分钟 | 2018-05-17 22:50:00
小时 | 2018-05-17 23:00:00
天 | 2018-05-18 00:00:00

'5 min'计算的“技巧”是在截断前减去1 µs的最小时间间隔，然后加上相应的时间间隔以有效地得到上限。EXTRACT()返回时间戳中的秒数，一个double precision小数位到微秒的数字。我们需要trunc()，因为plain cast tointeger会四舍五入，而我们需要截断。

这样我们就可以避免增加恰好落在上界的时间戳。不过，它有点脏，因为最小时间间隔是当前 Postgres 版本的实现细节。不过改变的可能性很小。有关的：

• 在 Rails 和 PostgreSQL 中完全忽略时区

'10 min'计算更简单ceil()，我们不需要通过减去 1 µs 来移动边界。清洁器。但是ceil()在我的测试中稍微贵一点。

扩展测试用例

WITH t(id, ts) AS (
   VALUES
     (1, timestamp '2018-05-17 22:45:30')  -- your input timestamps here
   , (2, timestamp '2018-05-20 00:00:00')
   , (3, timestamp '2018-05-20 00:00:00.000001')
   )
SELECT *
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1  -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min'  , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min' , to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'   , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'    , date_trunc('day' , ts1) + interval '1 day')
    , ('alt_day', ts1::date + 1)
   ) t2(interval, ceil_ts)
ORDER  BY id;

编号 | ts | ts1 | 间隔 | ceil_ts                   
-: | :------------------------- | :---------------------------- | :---------------- | :-------------
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 输入时间戳 | 2018-05-17 22:45:30       
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 5 分钟 | 2018-05-17 22:50:00       
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 10 分钟 | 2018-05-17 22:50:00       
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 小时 | 2018-05-17 23:00:00       
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 天 | 2018-05-18 00:00:00       
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | alt_day | 2018-05-18 00:00:00       
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 输入时间戳 | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 5 分钟 | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 10 分钟 | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 小时 | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 天 | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | alt_day | 2018-05-20 00:00:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 输入时间戳 | 2018-05-20 00:00:00.000001
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 5 分钟 | 2018-05-20 00:05:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 10 分钟 | 2018-05-20 00:10:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 小时 | 2018-05-20 01:00:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 天 | 2018-05-21 00:00:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | alt_day | 2018-05-21 00:00:00       

db<>在这里摆弄

我为全天添加了一个替代快捷方式：ts1::date + 1. 演员表date截断到一整天，我们可以加integer1 来增加一天。

函数包装

您后来透露您与 合作，因此我们可以从表达式timestamptz中删除。AT TIME ZONE

在我的测试中，声明函数STABLE产生了最佳性能，因为它允许函数内联。我本来希望IMMUTABLE是最好的，但是该声明对内部允许内联的内容更加挑剔。有关的：

• 功能性能

在我的测试中快一点：

CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling2(_tstz timestamptz, _int_seconds int)
  RETURNS timestamptz AS
$func$   
SELECT to_timestamp(trunc(extract(epoch FROM ($1 - interval '1 microsecond')))::int / $2 * $2 + $2)
$func$  LANGUAGE sql STABLE;

更清洁的国际海事组织：

CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _int_seconds int)
  RETURNS timestamptz AS
$func$   
SELECT to_timestamp(ceil(extract(epoch FROM $1) / $2) * $2)
$func$  LANGUAGE sql STABLE;

称呼：

SELECT f_tstz_interval_ceiling1(my_tstz, 600);  -- 600 = seconds in 10 min

为方便起见，您可以使用替代方法重载每个函数intervalas $2：

CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _interval interval)
  RETURNS timestamptz LANGUAGE sql STABLE AS
'SELECT f_tstz_interval_ceiling1($1, extract(epoch FROM $2)::int)';

只需使用提取的秒数调用第一个版本。然后你也可以调用：

SELECT f_tstz_interval_ceiling1(my_tstz, interval '10 min');

以每 3 个案例为例：

select now(), date_trunc('hour', now()), date_trunc('hour', now()) + (10 * round(extract(minute from now())/10) || ' minute')::interval;
              now              |       date_trunc       |        ?column?
-------------------------------+------------------------+------------------------
 2018-05-17 19:21:44.797717-05 | 2018-05-17 19:00:00-05 | 2018-05-17 19:20:00-05
(1 row)


select now(), date_trunc('day', now()), date_trunc('day', now()) + (1 * round(extract(hour from now())/1) || ' hour')::interval;
              now              |       date_trunc       |        ?column?
-------------------------------+------------------------+------------------------
 2018-05-17 19:22:34.508226-05 | 2018-05-17 00:00:00-05 | 2018-05-17 19:00:00-05
(1 row)


select now(), date_trunc('month', now()), date_trunc('month', now()) + (1 * round(extract(day from now())/1) || ' day')::interval;
              now              |       date_trunc       |        ?column?
-------------------------------+------------------------+------------------------
 2018-05-17 19:23:56.562104-05 | 2018-05-01 00:00:00-05 | 2018-05-18 00:00:00-05
(1 row)

所以基本上在

date_trunc('X', now()) + (Y * round(extract(Z from now())/Y) || ' Z')::interval

你应该更换：

• X 由间隔中使用的基数的下一个上项（例如：如果间隔使用天，X 必须是月）
• Y 是区间中使用的值
• Z 是间隔中使用的项目，如小时

当然替换now()为您正在处理的时间戳。

这可以在根据您提供的间隔值和步骤为您的特定时间戳创建的函数中抽象出来。

不确定是否了解您想要的确切截断，因此您可能需要替换round为ceil.

假设INTEGER_DATETIMES（构建选项）这几年来一直是时间戳存储的默认方法。

您可以创建一个从timestamp(或timestamp with timezone) 到的转换bigint，然后将它们作为整数的微秒数进行操作2000-01-01 00:00，然后再转换回timestamp( with timezone)

CREATE CAST (bigint AS timestamp) WITHOUT FUNCTION;
CREATE CAST (timestamp AS bigint) WITHOUT FUNCTION;

您现在可以使用整数运算（如/.

向前推进到下两周：

-- 1209600000000 microseconds is two weeks.

select (((now()::timestamp::bigint-1)/1209600000000+1) * (1209600000000))::timestamp;

所以

 create or replace function timestamp_ceil(timestamp,interval)
       returns timestamp 
       language plpgsql
   as $$ 
     declare 
       i  bigint = 1000000*extract( epoch from $2);
       ts bigint = $1::bigint;
     begin
       return (((ts-1)/i + (ts>0)::int )*i)::timestamp;
     end $$;

免责声明，这仅适用于固定大小的间隔，不规则大小的间隔，如 'month' 和 'year' 将被折叠成 '30 days' 和 '365.25 days' 。

(ts>0)::int需要，而不是1因为负数向零舍入，所以不需要增量。（从 boolean 到 int 的转换使我们得到 0 或 1 作为结果）