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

Does this answer your question? [Calculate sum time in Oracle](https://stackoverflow.com/questions/42580381/calculate-sum-time-in-oracle) — Maciej Los, Jun 22 '20 at 13:28

Gordon Linoff · Accepted Answer · 2020-06-22T13:40:22.380

0

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')

edited Jun 22 '20 at 13:40

answered Jun 22 '20 at 13:27

Gordon Linoff

1,242,037
58
646
786

I get an error ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND – Allmood Jun 22 '20 at 13:35
@Allmood . . . That would suggest that your columns are *timestamps* and not *dates*. For second precision, you might as well just convert to dates. – Gordon Linoff Jun 22 '20 at 13:39
I get an error again(( ORA-00937: not a single-group group function – Allmood Jun 22 '20 at 13:49
@Allmood . . . This addresses the question that you asked, which is how to add the sum of the differences in. The query in your question obviously needs a `group by` because it is otherwise syntactically incorrect. – Gordon Linoff Jun 22 '20 at 13:54

score 0 · Answer 2 · answered Jun 22 '20 at 14:05

0

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

answered Jun 22 '20 at 14:05

Popeye

35,427
4
10
31

score 0 · Answer 3 · answered Jun 22 '20 at 14:17

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

Add Sum() in query (oracle)

3 Answers3