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

Wildlife

I don't think so. First off, they have some date component, so they do not actually have values if 1 and 2 minutes. They have values like 1 minute after midnight on the 26th of July. Second, what would it mean to sum dates? What are you actually trying to accomplish? – Shanghai

Damn, I thought I was trying to sum dates. Actually, what I did was subtraction of two dates and then tried to SUM that. So i've got an error 'ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND'. Now where's the edit button? :) – Herbert

Some DBMS do support summation of INTERVAL values - IBM Informix Dynamic Server is one such. However, most do not. – Hecto

Answer 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.

Wildlife

As this is the answer which exactly and completely resolves your question you should accept it. – Comras

Answer 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!

Comras

That just gave me a brilliant idea why my SUM function failed. Since I was using TIMESTAMPs instead of DATETIME. Arithmetic operations with TIMESTAMPs produce INTERVAL type which SUM function can't calculate on Oracle. Since casting from TIMESTAMP to DATETIME is just too easy, my problem is solved on Oracle. For protability, I will probably use user function for doing the dirty work for me. – Herbert

For me if I put min*600 it is getting wrong.. ie 0 5:1:5.793000000, consider as 5 h and 10 mins, instead it should be 5 h and 1 min. So I replaced it with min*60 and worked. – Comenius

Answer 3

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.

Spada

Answer 4

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

Plutonian

Answer 5

I also do not think this is possible. Go with custom solutions that calculates the date value according to your preferences.

Stuartstub

Answer 6

You can also use this:

select  
  EXTRACT (DAY FROM call_end_Date - call_start_Date)*86400 + 
  EXTRACT (HOUR FROM call_end_Date - call_start_Date)*3600 + 
  EXTRACT (MINUTE FROM call_end_Date - call_start_Date)*60 + 
  extract (second FROM call_end_Date - call_start_Date) as interval
from table;

Sidwohl

Answer 7

You Can write you own aggregate function :-). Please read carefully http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dciaggfns.htm

You must create object type and its body by template, and next aggregate function what using this object:

create or replace type Sum_Interval_Obj as object
(
  -- Object for creating and support custom aggregate function
  duration interval day to second, -- In this property You sum all interval

  -- Object Init
  static function ODCIAggregateInitialize(
    actx IN OUT Sum_Interval_Obj
    ) return number,

  -- Iterate getting values from dataset 
  member function ODCIAggregateIterate(
    self         IN OUT  Sum_Interval_Obj,
    ad_interval  IN  interval day to second
    ) return number,

  -- Merge parallel summed data
  member function ODCIAggregateMerge(
    self IN OUT Sum_Interval_Obj,
    ctx2 IN Sum_Interval_Obj
  ) return number,

  -- End of query, returning summary result
  member function ODCIAggregateTerminate
  (
    self        IN  Sum_Interval_Obj,
    returnValue OUT interval day to second,
    flags       IN number
  ) return number

)
/

create or replace type body Sum_Interval_Obj is

  -- Object Init
  static function ODCIAggregateInitialize(
    actx IN OUT Sum_Interval_Obj
    ) return number
    is
  begin
    actx := Sum_Interval_Obj(numtodsinterval(0,'SECOND'));
    return ODCIConst.Success;
  end ODCIAggregateInitialize;

  -- Iterate getting values from dataset 
  member function ODCIAggregateIterate(
    self         IN OUT Sum_Interval_Obj,
    ad_interval  IN interval day to second
    ) return number
    is
  begin
    self.duration := self.duration + ad_interval; 
    return ODCIConst.Success;
  exception
    when others then
      return ODCIConst.Error;
  end ODCIAggregateIterate;

  -- Merge parallel calculated intervals
  member function ODCIAggregateMerge(
    self IN OUT Sum_Interval_Obj,
    ctx2 IN     Sum_Interval_Obj
    ) return number
    is
  begin
    self.duration := self.duration + ctx2.duration; -- Add two intervals
    -- return = All Ok!
    return ODCIConst.Success;
  exception
    when others then
      return ODCIConst.Error;
  end ODCIAggregateMerge;

  -- End of query, returning summary result
  member function ODCIAggregateTerminate(
    self        IN  Sum_Interval_Obj,
    returnValue OUT interval day to second,
    flags       IN number
    ) return number
    is
  begin
    -- return = All Ok, too!
    returnValue := self.duration;
    return ODCIConst.Success;
  end ODCIAggregateTerminate;

end;
/

-- You own new aggregate function:
CREATE OR REPLACE FUNCTION Sum_Interval(
    a_Interval interval day to second
    ) RETURN interval day to second
    PARALLEL_ENABLE AGGREGATE USING Sum_Interval_Obj;
/

Last, check your function:

select sum_interval(duration)
  from (select numtodsinterval(1,'SECOND')  as duration from dual union all
        select numtodsinterval(1,'MINUTE')  as duration from dual union all
        select numtodsinterval(1,'HOUR')    as duration from dual union all
        select numtodsinterval(1,'DAY')     as duration from dual);

Finally You can create SUM function, if you want.

Euphemism

Answer 8

Oracle Database 23c has extended the sum and avg functions to support interval types:

with rws as (
  select timestamp'2023-04-19 00:00:00' ts1,
         timestamp'2023-04-19 00:00:00' + level ts2
  connect by level <= 10
)
  select sum ( ts2 - ts1 ), avg ( ts2 - ts1 ) from rws;
  
SUM(TS2-TS1)        AVG(TS2-TS1)       
------------------- -------------------
+55 00:00:00.000000 +05 12:00:00.000000

Otoplasty