Epoch time difference when dates vary not showing correct value

Question

SELECT
 r.order_id
,c.order_time
,r.pickup_time
,EXTRACT(epoch FROM (r.pickup_time - c.order_time) / 60) AS diff
FROM runner_orders1 r
JOIN customer_orders1 c ON c.order_id = r.order_id
WHERE distance != 0;

order_id	order_time	pickup_time	diff
1	2020-01-01 18:05:02	2020-01-01 18:15:34	10.533333
2	2020-01-01 19:00:52	2020-01-01 19:10:54	10.033333
3	2020-01-02 23:51:23	2020-01-03 00:12:37	21.233333
3	2020-01-02 23:51:23	2020-01-03 00:12:37	21.233333
4	2020-01-04 13:23:46	2020-01-04 13:53:03	29.283333
4	2020-01-04 13:23:46	2020-01-04 13:53:03	29.283333

Here is my above sql output if you see when the timestamp days differ , the value is not correct. Please check and help.

 3 | 2020-01-02 23:51:23 | 2020-01-03 00:12:37 | 21.233333

Answer 1

Your query is returning exactly the correct results (at least to the 6 decimal digits). The problem seems to stem from your expectations. Apparently, you are looking for diff to be minuets and seconds, however, this is not what you are getting. By extracting epoch and dividing by 60 your results are in minuets and fractional part of a minuet. In the selected the difference in time between pickup_time and order_time is 21:14 (min:sec) the result returned turns out to be 21:13.99998 (min:sec.fractional seconds). This is just another reason using epoch is not really a good practice (see Epoch Mania for others).
Correct the result by just subtracting the dates (you are already doing so). This gives the result as an interval in Hours:Minuets:Seconds. For your example it returns 00:21:14 (see demo)

select r.order_id
     , c.order_time
     , r.pickup_time
     , r.pickup_time - c.order_time as diff
  from runner_orders1 r
  join customer_orders1 c on c.order_id = r.order_id
 where distance != 0;