# Function to compute difference between 2 times

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)? Commented Mar 21, 2021 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. Commented Mar 22, 2021 at 8:32

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? Commented Mar 21, 2021 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. Commented Mar 21, 2021 at 22:23
• @Vérace: Oh, and the function returns an `interval`, not `time`. That may clear up any confusion. Commented Mar 22, 2021 at 0: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. Commented Mar 22, 2021 at 8:29