SQL：是否可以对 INTERVAL 类型的字段进行 SUM() 计算？

Question

我正在尝试对 INTERVAL 进行求和。例如

SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL

是否可以编写一个同时适用于 Oracle 和 SQL Server 的查询？如果是这样，怎么办？

编辑：将日期更改为间隔

Answer 1

恐怕您将无法找到同时适用于 Oracle 和 MSSQL 的解决方案。日期运算在不同版本的 DBMS 上有很大不同。

无论如何，在 Oracle 中我们可以在简单的算术中使用日期。我们有一个函数 NUMTODSINTERVAL ，它将数字转换为一天到秒的间隔。那么让我们把它们放在一起。

简单的测试数据，两行包含大约相隔 12 个小时的日期对：

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>

至少这个结果以秒为单位！

Answer 2

好吧，经过一番努力，在 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 类型（这显然很奇怪）。因此，在 Oracle 上向 TIMESTAMP 添加零就像转换为日期一样，并且对 SQL Server DATETIME 类型没有任何影响。

谢谢你们！你真的很有帮助。

Answer 3

您无法对两个日期时间求和。这没有意义 - 即 15:00:00 加 23:59:00 等于什么？第二天某个时间？但是

您可以使用 SQL Server 中的 Dateadd() 等函数来添加时间增量。

Answer 4

在 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

Gives 的

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

操作，如果您想处理大于 24 小时的时间跨度，我认为您需要查看 CLR 集成和 TimeSpan 结构。绝对不便携！

编辑： SQL Server 2008 有一个 DateTimeOffset< /a> 数据类型可能有帮助，但不允许进行 SUM 计算或转换为浮点数

Answer 5

我也不认为这是可能的。使用根据您的喜好计算日期值的自定义解决方案。

Answer 6

您还可以使用这个：

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;

Answer 7

您可以编写自己的聚合函数:-)。请仔细阅读 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 函数。

Answer 8

Oracle Database 23c 将 sum 和 avg 函数扩展到 支持间隔类型：

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