SQL How to convert a date into hours per minute? Then subtract two of the colums (end-start) and set-CodePudding

I am wondering, how can I convert datetime into hours per minute? I want the following. To minus the end_trip with the start_trip that is in hours and minutes. Moreover, I want to create a new column for the result. However, I am getting nowhere. I want to use basic arithmetic for this not some built in functions like DATEDIFF. Please help.

Below is my code

INSERT INTO TRIP VALUES( 
TO_DATE('26-01-2022 14:00:00', 'DD/MM/YYYY HH24:MI:SS'), - start_time 
TO_DATE('26-01-2022 14:50:00', 'DD/MM/YYYY HH24:MI:SS')  - end_time
; 

SELECT 
trip_start_actual as start_time, trip_end_actual as end_time, (trip_end_actual - trip_start_actual)
from trip;
commit;

CodePudding user response：

Subtracting two dates in Oracle will yield a difference in fractional days. To get minutes from that, simply multiply that difference by 1440:

SELECT trip_start_actual AS start_time,
       trip_end_actual AS end_time,
       1440 * (trip_end_actual - trip_start_actual) AS num_minutes
FROM trip;

CodePudding user response：

when we subtract two dates in oracle the difference integer also comes in days. we need to multiply that by 24 to get hours and multiply by 24 * 60 = 1440 to get minutes and multiply by 24 * 60 * 60 = 86400 to get seconds.

SELECT 
trip_start_actual as start_time, trip_end_actual as end_time, 
round ((trip_end_actual - trip_start_actual) * 24 * 60) as 
time_diff_minutes from trip;