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.