SQL:是否可以对间隔类型的()字段求和?

恐怕你会对一个在Oracle和MSSQL中都能工作的解决方案不太走运。数据运算在DBMS的不同风格上是非常不同的。

简单的测试数据,两行日期相隔大约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> select numtodsinterval(sum(d1-d2), 'DAY')
  2  from t42
  3  /

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

SQL>

一天多一点,这是我们所期望的。

“编辑:将日期更改为间隔”

使用时间戳列有点费力,但我们仍然可以使用相同的技巧。

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

这将返回一个float(对我来说很好),它表示oracleantsql服务器上的天数。

我之所以给这两个日期都加零,是因为在我的例子中,oracledb上的date列是TIMESTAMP类型,而sqlserver上的DATETIME列是DATETIME类型(这显然很奇怪)。因此,在Oracle上向TIMESTAMP中添加零的工作方式与强制转换为date的工作方式一样,对sqlserverdatetime类型没有任何影响。

你不能把两次约会加起来。这是没有意义的-即15:00:00加23:59:00等于多少?第二天什么时候?等

但是您可以通过在SQLServer中使用类似Dateadd()的函数来添加时间增量。

在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

TimeSpan 结构。绝对不是便携的!

编辑: DateTimeOffset 数据类型可能会有所帮助,但这两者都不允许 SUM

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

您也可以使用此功能:

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函数。