Rounding datetimes and timestamps in PostgreSQL

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.

		 ts timestamptz
		,round_secs int
                ) RETURNS timestamptz AS $$
                _mystamp timestamp;
                _round_secs decimal;

	_round_secs := round_secs::decimal;

	_mystamp := timestamptz 'epoch' 
			+ ROUND((EXTRACT(EPOCH FROM ts))::int / _round_secs) * _round_secs
			* INTERVAL '1 second';

	RETURN _mystamp;

SELECT * FROM round_timestamp('2010-03-04 11:39:11', 900);
 2010-03-04 11:45:00-05
(1 row)

3 Replies to “Rounding datetimes and timestamps in PostgreSQL”

  1. 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) ;

    date_trunc(‘hour’, B.tstamp) + N.nearest as rounded
    report.billing B,
    report.nearest N
    extract(minute from B.tstamp) = N.”minute” ;

    Results are :

    ——————- ——– ——————-
    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.

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *