SQL: Возможно ли SUM() полей типа INTERVAL?

Боюсь, вам не повезет с решением, которое работает как в Oracle, так и в MSSQL. Арифметика дат — это то, что сильно отличается в разных вариантах СУБД.

Во всяком случае, в Oracle мы можем использовать даты в простой арифметике. И у нас есть функция NUMTODSINTERVAL, которая превращает число в интервал от дня до секунды. Итак, давайте сложим их вместе.

Простые тестовые данные, две строки с парами дат с разницей примерно в двенадцать часов:

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>

Простой SQL-запрос, чтобы найти сумму прошедшего времени:

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

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

SQL>

Чуть больше суток, чего мы и ожидали.

"Изменить: изменена ДАТА на ИНТЕРВАЛ"

Работа со столбцами TIMESTAMP немного более трудоемка, но мы все же можем проделать тот же трюк.

В следующем образце. T42T такой же, как T42, только столбцы имеют TIMESTAMP, а не DATE для своего типа данных. Запрос извлекает различные компоненты DS INTERVAL и преобразует их в секунды, которые затем суммируются и преобразуются обратно в 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>

По крайней мере, это результат в круглых секундах!

Хорошо, после небольшого ада, с помощью ответов stackoverflowers я нашел решение, которое соответствует моим потребностям.

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

Это возвращает число с плавающей запятой (что совершенно нормально для меня), которое представляет дни как на Oracle ant SQL Server.

Причина, по которой я добавил ноль к обеим DATE, заключается в том, что в моем случае столбцы даты в Oracle DB имеют тип TIMESTAMP, а на SQL Server — тип DATETIME (что, очевидно, странно). Таким образом, добавление нуля к TIMESTAMP в Oracle работает так же, как приведение к дате, и не влияет на тип DATETIME SQL Server.

Спасибо вам, ребята! Вы были очень полезны.

Вы не можете суммировать два даты и времени. Это не имело бы смысла - то есть чему равно 15:00:00 плюс 23:59:00? Где-то на следующий день? и т.д

Но вы можете добавить приращение времени, используя такую ​​функцию, как Dateadd() в SQL Server.

В SQL Server, если ваши индивидуальные промежутки времени меньше 24 часов, вы можете сделать что-то вроде

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

Дает

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

Если вы хотите обрабатывать промежутки времени более 24 часов, я думаю, вам нужно взглянуть на интеграцию CLR и TimeSpan. Однозначно не портативный!

Изменить: SQL Server 2008 имеет DateTimeOffset тип данных, который может помочь, но не позволяет ни SUMming, ни приведению к плавающим значениям

Я тоже не думаю, что это возможно. Используйте собственные решения, которые вычисляют значение даты в соответствии с вашими предпочтениями.

Вы также можете использовать это:

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;

Вы можете написать свою собственную агрегатную функцию :-). Пожалуйста, внимательно прочитайте http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dciaggfns.htm

Вы должны создать тип объекта и его тело по шаблону, а следующую агрегатную функцию использовать этот объект:

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;
/

Наконец, проверьте свою функцию:

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

Наконец, вы можете создать функцию SUM, если хотите.