PostgreSQL Function – Compute Difference Between Two Times

functionsintervalpostgresqltime

I want to create a simple function in Postgres to find the difference between 2 TIME – not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I have commented out seconds, just working on minutes.

CREATE OR REPLACE FUNCTION time_diff(hr INT, min INT, sec INT, exp_hr INT)
RETURNS INT
LANGUAGE plpgsql AS
$$
DECLARE
    cur_time    TIME;
    expire_time TIME;

    diff_interval INTERVAL;
    diff INT = 0;
BEGIN
    cur_time    = CONCAT(hr,  ':',  min, ':', sec) AS TIME; -- cast hour, minutes and seconds to TIME
    expire_time = CONCAT(exp_hr, ':00:00') AS TIME;         -- cast expire hour to TIME

    -- MINUS operator for TIME returns interval 'HH:MI:SS;
    diff_interval = expire_time - cur_time;

    diff = DATE_PART('hour', diff_interval);

    diff = diff * 60 + DATE_PART('minute', diff_interval);

    --diff = diff * 60 + DATE_PART('second', diff_interval);

    RETURN diff;
END;
$$;

Example:
01:15:00 – 02:00:00 should give me 45 minutes, so I do the following and I get the correct answer.

select * from time_diff(1, 15, 0, 2);

However, if I do this: 23:15:00 – 01:00:00 – the should give me 105 minutes (60 + 45).

select * from time_diff(23, 15, 0, 1);

But the result I am getting is -1335. I am trying to work out where I have gone wrong here.

Also I am invoking DATE_PART functions, this seems to be a quite an expensive process in terms of CPU usage. Is there a better way of optimising this function. With the first example I am getting results in 0.007s on 2018 i7 Mac mini. Although I do think this function is quick, but could it be better?

Best Answer

Assuming exp_hr is always ahead in time, but never more than 24 hours.

I suggest to return an interval for simplicity. Then it works for hours, minutes, seconds, microseconds etc. alike.

Besides correct, this should be faster by orders of magnitude:

CREATE OR REPLACE FUNCTION f_time_diff(hr INT, min INT, sec INT, exp_hr INT)
  RETURNS interval
  LANGUAGE sql PARALLEL SAFE IMMUTABLE AS
$func$
SELECT CASE WHEN hr >= exp_hr       -- wrap around midnight
            THEN 24 + exp_hr - hr
            ELSE exp_hr - hr
            END * interval '1 hour'
                - interval '1 min' * min
                - interval '1 sec' * sec;
$func$;

Note that this returns 24h for input = output exactly. You might decide otherwise for the corner case.

Demo:

test=*> SELECT f_time_diff( 1, 15, 0, 2) AS example1
test-*>      , f_time_diff(23, 15, 0, 1) AS example2
test-*>      , f_time_diff( 1, 15, 0, 1) AS example3
test-*>      , f_time_diff( 1,  0, 0, 1) AS example4;
 example1 | example2 | example3 | example4 
----------+----------+----------+----------
 00:45:00 | 01:45:00 | 23:45:00 | 24:00:00
(1 row)

If you actually need the number of minutes as integer, (truncating seconds if any), extract the epoch and use integer division:

SELECT EXTRACT(epoch FROM f_time_diff(23,15,0,1))::int / 60 AS example2;  -- 105

(Or build that into the function.)

db<>fiddle here

The most expensive part is the type conversion now. If you input time / interval, it gets much faster, yet. Or better yet: timestamp or timestamptz, then it gets trivial and you wouldn't need a function to begin with ...

Concatenating strings before casting to time (like you had it) is much more expensive, yet.

Related Question