PostgreSQL – How to Round Time to the Upper Multiple of an Arbitrary Time Interval

date mathdatetimeintervalpostgresql

Example:

  • If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00.
  • If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '10 minute', then the desired output is 2018-05-17 22:50:00.
  • If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '1 hour', then the desired output is 2018-05-17 23:00:00.
  • If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '1 day', then the desired output is 2018-05-18 00:00:00.

Best Answer

Assuming data type timestamp. Some details are different for date or timestamptz.

A general solution for any time interval can be based on the epoch value and integer division to truncate. Covers all your examples.

The special difficulty of your task: you want the ceiling, not the floor (which is much more common). Exercise care with lower and upper bounds to avoid corner case bugs: you don't want to increment exact floor values. (Or so I assume.)

For common time intervals built into date_trunc() (like 1 hour and 1 day in your examples) you can use a shortcut. The definition of days depends on the time zone setting of the session with timestamptz (but not with timestamp).

A natural alternative is with ceil(). A bit slower in my tests, but cleaner.

Short demo

-- 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);
interval        | ceil_ts            
:-------------- | :------------------
input timestamp | 2018-05-17 22:45:30
5 min           | 2018-05-17 22:50:00
10 min          | 2018-05-17 22:50:00
hour            | 2018-05-17 23:00:00
day             | 2018-05-18 00:00:00

The "trick" for the '5 min' calculation is to subtract the minimum time interval of 1 µs before truncating, and then add the respective time interval to effectively get the ceiling. EXTRACT() returns the number of seconds in the timestamp, a double precision number with fractional digits down to microseconds. We need trunc() because the plain cast to integer would round, while we need to truncate.

This way we avoid incrementing timestamps that fall on the upper bound exactly. It is slightly dirty, though, because the minimum time interval is an implementation detail of current Postgres versions. Very unlikely to change though. Related:

The '10 min' calculation is simpler with ceil(), we don't need to shift bounds by subtracting 1 µs. Cleaner. But ceil() is slightly more expensive in my tests.

Extended test case

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;
id | ts                         | ts1                        | interval        | ceil_ts                   
-: | :------------------------- | :------------------------- | :-------------- | :-------------------------
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | input timestamp | 2018-05-17 22:45:30       
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | 5 min           | 2018-05-17 22:50:00       
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | 10 min          | 2018-05-17 22:50:00       
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | hour            | 2018-05-17 23:00:00       
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | day             | 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 | input timestamp | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | 5 min           | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | 10 min          | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | hour            | 2018-05-20 00:00:00       
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | day             | 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        | input timestamp | 2018-05-20 00:00:00.000001
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | 5 min           | 2018-05-20 00:05:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | 10 min          | 2018-05-20 00:10:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | hour            | 2018-05-20 01:00:00       
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | day             | 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<>fiddle here

I added an alternative shortcut for full days: ts1::date + 1. The cast to date truncates to the full day and we can add integer 1 to add a day.

Function wrapper

You later disclosed you work with timestamptz, so we can drop AT TIME ZONE from the expression.

In my tests declaring the function STABLE yielded best performance because it allowed function inlining. I would have expected IMMUTABLE to be best, but that declaration is more picky about what's allowed inside to be inlined. Related:

A bit faster in my tests:

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;

Cleaner IMO:

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;

Call:

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

For convenience, you could overload each function with an alternative taking an interval as $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)';

Just invoking the first version with extracted seconds. Then you can also call:

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