I’m working on a time billing application and needed a way to round punches to the nearest 15 minute increment from within the Postgres DB. The function below is the most elegant solution I could come up with. It could be easily modified to always round-up or round-down by replacing ROUND() with either CEIL() or FLOOR(), respectively.
CREATE OR REPLACE FUNCTION round_timestamp(
ts timestamptz
,round_secs int
) RETURNS timestamptz AS $$
DECLARE
_mystamp timestamp;
_round_secs decimal;
BEGIN
_round_secs := round_secs::decimal;
_mystamp := timestamptz 'epoch'
+ ROUND((EXTRACT(EPOCH FROM ts))::int / _round_secs) * _round_secs
* INTERVAL '1 second';
RETURN _mystamp;
END; $$ LANGUAGE plpgsql IMMUTABLE;
SELECT * FROM round_timestamp('2010-03-04 11:39:11', 900);
round_timestamp
------------------------
2010-03-04 11:45:00-05
(1 row)
Hi,
You may like this solution also…
== Solution #1
You could build a 60 rows table with two columns : “minute” and “nearest”. Those 60 rows are pre-computed. Make “minute” the PK.
By joining, you can get the nearest increment, and then you can compute back the rounded timestamp.
create table report.nearest
(
“minute” integer,
nearest time,
constraint nearest_pk primary key (“minute”)
) ;
—
— To be done once, when application is deployed
—
insert into report.nearest values (0, time ’00:00′) ;
insert into report.nearest values (1, time ’00:00′) ;
…
insert into report.nearest values (14, time ’00:00′) ;
insert into report.nearest values (15, time ’00:15′) ;
insert into report.nearest values (16, time ’00:16′) ;
…
create table report.billing
(
tstamp timestamp,
whatever integer
) ;
insert into report.billing values(timestamp ‘2001-02-16 20:14:12’, 1) ;
insert into report.billing values(timestamp ‘2001-02-16 20:15:34’, 2) ;
select
B.tstamp,
N.nearest,
date_trunc(‘hour’, B.tstamp) + N.nearest as rounded
from
report.billing B,
report.nearest N
where
extract(minute from B.tstamp) = N.”minute” ;
Results are :
TSTAMP NEAREST ROUNDED
——————- ——– ——————-
2001-02-16 20:14:12 00:00:00 2001-02-16 20:00:00
2001-02-16 20:15:34 00:15:00 2001-02-16 20:15:00
The underlying principle is simple: you can tradeoff storage for computing power (and way back). Here, storage is table report.nearest and computing power is your round_timestamp function.
Btw, in database terminology, I guess “nearest” is a dimension table and “billing” is the fact table.
I don’t know if this solution “is the most elegant” as you state but it is “full SQL”: usually, this is to be prefered because database engines are better at “crunching” sets of data and optmizing SQL requests rather than running procedural code.
== Solution #2
If you manage lots of billing records within a batch process (I mean 100K or millions rows maybe), you may consider to compute more data ahead of the batch time window.
Maybe extract(minute from B.tstamp) could be precomputed in a new column when the billing record is created ? The effect on whole performance of the application could be good … or worse, it depends on the environment and must be validated.
Note: this denormalizes the data model to get better performance and should be documented.
Hope this helps.
Interesting solution… Thanks for taking the time to write it up and post!
I’m working on the same thing like you, I have a table with the beginning and ending of the tasks. Now I want to calculate the time I worked on a task (ending-beginning), that means I’m getting an interval. How can I round this interval now on 15minutes? Should be very similar to your code, but I don’t get it.
greetz & thanks