3

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
7

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');
  • 1
    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
  • 1
    @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
  • 1
    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
2

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.