# How do I round time to the upper multiple of an arbitrary time interval?

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.
• Is the interval part something that should be a variable, so that any value works, or you need it to work only for a single case, fixed in advance? – Patrick Mevzek May 18 '18 at 0:16
• @PatrickMevzek variable. – Gajus May 18 '18 at 7:24

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');
``````
• Thank you. I needed it variable, therefore I have abstracted the above logic using a function as follows gist.github.com/gajus/1ffbcc7b042845e421f7949f9eb7d679. – Gajus May 18 '18 at 8:15
• @Gajus: Your function implementation looks flawless. But it should be faster as `STABLE` SQL function (at least in my tests). So you work with `timestamptz`? Should be declared in the question. I added another idea with `ceil()` and function implementations. – Erwin Brandstetter May 18 '18 at 16:44
• this is doing computations in floating point so you may get unexpected results for some intervals less than one second. – Jasen May 19 '18 at 3:21
• @Jasen: Yes, the `ceil()` variant does and your concern might apply (would love to see a demo with that actually happening). All other variants don't, except for `ts - interval '1 microsecond'` which is not subject to rounding issues with the possible range of values. – Erwin Brandstetter May 19 '18 at 15:23
• `extract(... from ... )` gets you a (double) float. I was thinking of a pure bigint solution but it's probably too messy to be worth considering. – Jasen May 20 '18 at 2:14

For each 3 cases you take as example:

``````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)
``````

So basically in

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

you should replace:

• X by the next upper item from the base used in the interval (ex: if interval uses day, X must be month)
• Y is the value used in the interval
• Z is the item used in the interval, like hour

and of course replace `now()` by the timestamp your are dealing with.

This could be abstracted in the function that creates this based on the interval value and step you provide, for your specific timestamp.

Not sure to have understood the exact truncation you want, so you may need to replace `round` by `ceil`.

assuming `INTEGER_DATETIMES` (build option) which has been default method of timestamp storage for serveral years now.

you can create a cast from `timestamp` (or `timestamp with timezone` ) to `bigint` and then manipulate them as integer numbers of microseconds since `2000-01-01 00:00` and then cast back to `timestamp` (`with timezone`)

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

you can now manipulate the internal representation of timestamp using integer operations like `/`.

round forwards to next fortnight:

``````-- 1209600000000 microseconds is two weeks.

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

so

`````` 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 \$\$;
``````

Disclaimer, this only works for intervals of constant size, irregularly sized intervals like 'month' and 'year' will be folded into a '30 days' and '365.25 days' .

`(ts>0)::int` is needed instead of `1` because negative numbers round towards zero, so increment is not wanted there. (the cast from boolean to int gets us 0 or 1 as the result)

• No time to play with it right now, but this is interesting. – Erwin Brandstetter May 23 '18 at 0:55