Not sure to have understood the exact truncation you want, so you may need to replace round by ceil.
Not sure to have understood the exact truncation you want, so you may need to replace round by ceil.
For each 3 cases you take as example:
- 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 useused 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.
For each cases:
- 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 use in the interval, like hour
and of course replace now() by the timestamp your are dealing with.
For each 3 cases you take as example:
- 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.
For each cases:
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 use in the interval, like hour
and of course replace now() by the timestamp your are dealing with.
lang-sql