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!