oracle SQL：是否可以使用 INTERVAL 类型的 SUM() 字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3352057/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Is it possible to SUM() fields of INTERVAL type?
提问by ?ygimantas
I am trying to sum INTERVAL. E.g.
我正在尝试总结 INTERVAL。例如
SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL
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?
是否可以编写一个既适用于 Oracle 又适用于 SQL Server 的查询？如果是这样，如何？
Edit: changed DATE to INTERVAL
编辑：将日期更改为间隔
回答by APC
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.
恐怕您对同时适用于 Oracle 和 MSSQL 的解决方案会不走运。日期算术在 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.
无论如何，在 Oracle 中，我们可以在简单的算术中使用日期。我们有一个函数 NUMTODSINTERVAL，它可以将一个数字转换为 DAY TO SECOND INTERVAL。所以让我们把它们放在一起。
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 查询：
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.
使用 TIMESTAMP 列有点费力，但我们仍然可以使用相同的技巧。
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:
在以下示例中。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>
At least this result is in round seconds!
至少这个结果是在几回合内！
回答by ?ygimantas
Ok, after a bit of hell, with the help of the stackoverflowers' answers I've found the solution that fits my needs.
好吧，经过一番苦苦挣扎后，在 stackoverflowers 的答案的帮助下，我找到了适合我需求的解决方案。
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.
这将返回一个浮点数（对我来说完全没问题），它代表 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.
我向两个 DATE 添加零的原因是因为在我的情况下，Oracle DB 上的日期列是 TIMESTAMP 类型，而 SQL Server 上的日期列是 DATETIME 类型（这显然很奇怪）。因此，在 Oracle 上向 TIMESTAMP 添加零就像转换为日期一样，它对 SQL Server DATETIME 类型没有任何影响。
Thank you guys! You were really helpful.
谢谢你们！你真的很有帮助。
回答by Farthest Shore
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
您不能将两个日期时间相加。这没有意义 - 即 15:00:00 加上 23:59:00 等于什么？第二天的某个时间？等等
But you can add a time increment by using a function like Dateadd() in SQL Server.
但是您可以使用 SQL Server 中的 Dateadd() 等函数来添加时间增量。
回答by Martin Smith
In SQL Server as long as your individual timespans are all less than 24 hours you can do something like
在 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
If you wanted to handle timespans greater than 24 hours I think you'd need to look at CLR integration and the TimeSpanstructure. Definitely not portable!
如果您想处理超过 24 小时的时间跨度，我认为您需要查看 CLR 集成和TimeSpan结构。绝对不便携！
Edit:SQL Server 2008 has a DateTimeOffsetdatatype that might help but that doesn't allow either SUMming or being cast to float
编辑：SQL Server 2008 有一个DateTimeOffset数据类型可能有帮助，但不允许SUMming 或被强制转换为浮动
回答by Dick Lampard
I also do not think this is possible. Go with custom solutions that calculates the date value according to your preferences.
我也不认为这是可能的。使用根据您的喜好计算日期值的自定义解决方案。
回答by user2671162
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;
回答by Zabobonin.S
You Can write you own aggregate function :-). Please read carefully http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dciaggfns.htm
您可以编写自己的聚合函数:-)。请仔细阅读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.
最后，如果需要，您可以创建 SUM 函数。
