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

Solution 1

Ok, after a bit of hell, with the help of the stackoverflowers' answers I've found the solution that fits my needs.

SELECT
  SUM(CAST((DATE1 + 0) - (DATE2 + 0) AS FLOAT) AS SUM_TURNAROUND
FROM MY_BEAUTIFUL_TABLE
GROUP BY YOUR_CHOSEN_COLUMN

This returns a float (which is totally fine for me) that represents days both on Oracle ant SQL Server.

The reason I added zero to both DATEs is because in my case date columns on Oracle DB are of TIMESTAMP type and on SQL Server are of DATETIME type (which is obviously weird). So adding zero to TIMESTAMP on Oracle works just like casting to date and it does not have any effect on SQL Server DATETIME type.

Thank you guys! You were really helpful.

Solution 2

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!

Solution 3

In SQL Server as long as your individual timespans are all less than 24 hours you can do something like

WITH TIMES AS
(
SELECT CAST('01:01:00' AS DATETIME) AS TimeSpan
UNION ALL
SELECT '00:02:00'
UNION ALL
SELECT '23:02:00'
UNION ALL
SELECT '17:02:00'
--UNION ALL SELECT '24:02:00' /*This line would fail!*/
),
SummedTimes As
(
SELECT cast(SUM(CAST(TimeSpan AS FLOAT)) as datetime) AS [Summed] FROM TIMES
)
SELECT 
    FLOOR(CAST(Summed AS FLOAT)) AS D,
    DATEPART(HOUR,[Summed]) AS H,
    DATEPART(MINUTE,[Summed]) AS M,
    DATEPART(SECOND,[Summed]) AS S
FROM SummedTimes

Gives

D           H           M           S
----------- ----------- ----------- -----------
1           17          7           0

If you wanted to handle timespans greater than 24 hours I think you'd need to look at CLR integration and the TimeSpan structure. Definitely not portable!

Edit: SQL Server 2008 has a DateTimeOffset datatype that might help but that doesn't allow either SUMming or being cast to float

Solution 4

You can't sum two datetimes. It wouldn't make sense - i.e. what does 15:00:00 plus 23:59:00 equal? Some time the next day? etc

But you can add a time increment by using a function like Dateadd() in SQL Server.

View more solutions