2

i have a query which gets a time difference from two timestamps, which basically looks like this

SELECT (time_a - time_b) FROM t_tabel WHERE t_some_id IN('1','2','3');

then I get the output

0 0:4:0.0
1 0:15:0.0
0 0:20:0.0

DAYS HOURS24H:MINUTES:SECONDS:MILLISECONDS

Now I just want to sum up these times, I've tried several things but nothing wont work.

SELECT sum(time_a - time_b) FROM t_tabel WHERE t_some_id IN('1','2','3');

gives me: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SELECT sum(to_dsinterval(time_a - time_b)) FROM t_tabel
WHERE t_some_id IN('1','2','3');

gives me: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SELECT sum(SELECT time_a - time_b FROM t_tabel
WHERE t_some_id IN('1','2','3')) from dual;

gives me: missing expression

SELECT sum(SELECT to_dsinterval(time_a - time_b) FROM t_tabel
WHERE t_some_id IN('1','2','3')) from dual;

gives me: missing expression

Somehow it's got to work.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
user2358925
  • 27
  • 2
  • 1
    Are all your fractional seconds values really zero? If so they could be stored as `DATE`, or could be cast to `DATE` to do the summation? – Alex Poole May 07 '13 at 15:37
  • 1
    possible duplicate of [How to average time intervals?](http://stackoverflow.com/questions/450581/how-to-average-time-intervals) (OK, that's for `AVG`, but the principal is the same for `SUM`, and it links to an AskTom article that tackles both...) – Alex Poole May 07 '13 at 15:38
  • Well spotted! I guess one moral here is to use datatypes as accurate as your problem requires. In this case it seems that `date` would be more than fine and `timestamp` is overkill. On the other hand, there are cases when I would want to use `timestamp with timezone` because I want the timestamp handling even if I don't need the precision. – Colin 't Hart May 07 '13 at 15:39
  • 1
    @Colin'tHart - indeed, but I've seen `TIMESTAMP` used because it isn't obvious (if coming from another DBMS maybe) that `DATE` can also store a time, particularly if the `NLS_DATE_FORMAT` doesn't show the time part. – Alex Poole May 07 '13 at 15:42

1 Answers1

4

The problem is that Oracle supplies us with these wonderful interval datatypes but doesn't provide the aggregate functions needed to sum or avg them!

Luckily Tom Kyte has written the necessary functions to implement this, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5269744394385

I found this because I've used this in the past and found it again by Googling "oracle interval aggregation".

Colin 't Hart
  • 7,372
  • 3
  • 28
  • 51