SQL: Is it possible to SUM() fields of INTERVAL type?

Question

I am trying to sum INTERVAL. E.g.

SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL

Is it possible to write a query that would work both on Oracle and SQL Server? If so, how?

Edit: changed DATE to INTERVAL

Answer 1

I'm afraid you're going to be out of luck with a solution which works in both Oracle and MSSQL. Date arithmetic is something which is very different on the various flavours of DBMS.

Anyway, in Oracle we can use dates in straightforward arithmetic. And we have a function NUMTODSINTERVAL which turns a number into a DAY TO SECOND INTERVAL. So let's put them together.

Simple test data, two rows with pairs of dates rough twelve hours apart:

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
  2  /

Session altered.

SQL> select * from t42
  2  /

D1                   D2
-------------------- --------------------
27-jul-2010 12:10:26 27-jul-2010 00:00:00
28-jul-2010 12:10:39 28-jul-2010 00:00:00

SQL>

Simple SQL query to find the sum of elapsed time:

SQL> select numtodsinterval(sum(d1-d2), 'DAY')
  2  from t42
  3  /

NUMTODSINTERVAL(SUM(D1-D2),'DAY')
-----------------------------------------------------
+000000001 00:21:04.999999999

SQL>

Just over a day, which is what we would expect.

---

"Edit: changed DATE to INTERVAL"

Working with TIMESTAMP columns is a little more labourious, but we can still work the same trick.

In the following sample. T42T is the same as T42 only the columns have TIMESTAMP rather than DATE for their datatype. The query extracts the various components of the DS INTERVAL and converts them into seconds, which are then summed and converted back into an INTERVAL:

SQL> select numtodsinterval(
  2              sum(
  3                  extract (day from (t1-t2)) * 86400
  4                   + extract (hour from (t1-t2)) * 3600
  5                   + extract (minute from (t1-t2)) * 600
  6                   + extract (second from (t1-t2))
  7            ), 'SECOND')
  8  from t42t
  9  /

NUMTODSINTERVAL(SUM(EXTRACT(DAYFROM(T1-T2))*86400+EXTRACT(HOURFROM(T1-T2))*
---------------------------------------------------------------------------
+000000001 03:21:05.000000000

SQL>

At least this result is in round seconds!