Function to compute difference between 2 times

Question

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?

Please disclose your version of Postgres. Why integer numbers for input and output, instead of time, interval or timestamp values? Also, `exp_hr` is always ahead in time and never wraps around (> 24h)? — Erwin Brandstetter, Mar 21 '21 at 15:09
@ErwinBrandstetter I am using Postgres 12.3, and in this case `exp_hr` never exceeds 24. However, I can see in coding that you've wrapped this. Very handy. — Tomus85, Mar 22 '21 at 08:32

Erwin Brandstetter · Accepted Answer · 2021-03-22T22:45:40.510

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.

In your example 4, you have a time of 24:00:00 - I thought that this is impossible, except in the very particular case of a leap second being added? Again, my understanding was that a day started at 00:00:00 (zero-hundred hours) and could go up to 23:59:59.9999... recurring to ∞ , but that the time in a given day could never reach 24:00:00 - that's shown as 00:00:00 the **following** day? — Vérace, Mar 21 '21 at 19:17
@Vérace: Maybe you missed this bit: `Note that this returns 24h for input = output exactly.` It depends on undisclosed requirements how this should turn out. This is just the simplest way. — Erwin Brandstetter, Mar 21 '21 at 22:23
@Vérace: Oh, and the function returns an `interval`, not `time`. That may clear up any confusion. — Erwin Brandstetter, Mar 22 '21 at 00:41
@ErwinBrandstetter thank you for the answer, this seems to be a better solution. And yes `interval` seems to be a better type with play with. Also it is quick. — Tomus85, Mar 22 '21 at 08:29

Function to compute difference between 2 times

1 Answers1