Add Sum() in query (oracle)

Question

I have a working request:

SELECT Drivers.Surname, Drivers.Name, Waybills.StartTime,
       TO_CHAR(TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')+(FinishTime - StartTime),'hh24:mi:ss') AS run_time
FROM Waybills JOIN
     Drivers
     ON Drivers.Id = Waybills.DriverId
WHERE Waybills.StartTime > SYSDATE-7 ORDER BY Name ASC;

But I can’t add sum in this place sum(TO_CHAR(TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')+(FinishTime - StartTime),'hh24:mi:ss')) AS run_time

Answer 1

I think you want to do the sum on the difference, then add to a date and convert to a string:

TO_CHAR(date '1970-01-01' + sum(FinishTime - StartTime), 'hh24:mi:ss')

Based on your comment, your columns are timestamps not dates. You are only looking for second precision, so you might as well just convert to dates:

TO_CHAR(date '1970-01-01' + sum(cast(FinishTime as date) - cast(StartTime as dte)), 'hh24:mi:ss')

Answer 2

You are converting date to char and adding difference of timestamp which is wrong.

You must do something like this:

DATE'1970-01-01' +(cast(FinishTime as date) - cast(StartTime as date)) AS run_time

Answer 3

You can try some workaround like this:

trunc( mod(mod(end_date - start_date,1)*24,1)*60 ) as mins , mod(mod(mod(end_date - start_date,1)*24,1)*60,1)*60 as secs

For the rest you can try these: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084

After getting the result, you can easily wrap it to get a string format [with e.g. to_char(x, 'yyyy-mm-dd')]

Here are some more examples:

Calculate difference between 2 date / times in Oracle SQL