postgresql date_trunc to arbitrary precision?

Question

postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?

I know one trick is to convert the time stamp to epoch, do some math, then convert back. But I don't like it.

Answer 1

There is no function you want, but as said in postgresql wiki you can define function for youself:

CREATE OR REPLACE FUNCTION round_time_10m(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + INTERVAL '10 min' * ROUND(date_part('minute', $1) / 10.0) 
$$ LANGUAGE SQL;

Generally rounding up to $2 minutes:

CREATE OR REPLACE FUNCTION round_time_nm(TIMESTAMP WITH TIME ZONE, INTEGER) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + ($2 || ' min')::INTERVAL * ROUND(date_part('minute', $1) / $2) 
$$ LANGUAGE SQL;

Answer 2

Postgres 14 date_bin.
Example use

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');

Result: 2020-02-11 15:30:00

The timescaleDb extension has a time_bucket function that supports day, minutes and lower intervals.