SQL 计算sql查询中的持续时间总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/891833/
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
Calculate the Sum of duration in sql query
提问by Amit Goyal
I have a table which has two columns start time and end time. I am able to calculate the time duration for each row but I also want to get the total duration. how to do this.
我有一个表，它有两列开始时间和结束时间。我能够计算每一行的持续时间，但我也想获得总持续时间。这该怎么做。
Thanks
谢谢
回答by Rob van Wijk
Your columns are of datatype TIMESTAMP, like this:
您的列的数据类型为 TIMESTAMP，如下所示：
SQL> create table mytable (start_time,end_time)
2 as
3 select to_timestamp('2009-05-01 12:34:56','yyyy-mm-dd hh24:mi:ss')
4 , to_timestamp('2009-05-01 23:45:01','yyyy-mm-dd hh24:mi:ss')
5 from dual
6 union all
7 select to_timestamp('2009-05-01 23:45:01','yyyy-mm-dd hh24:mi:ss')
8 , to_timestamp('2009-05-02 01:23:45','yyyy-mm-dd hh24:mi:ss')
9 from dual
10 union all
11 select to_timestamp('2009-05-01 07:00:00','yyyy-mm-dd hh24:mi:ss')
12 , to_timestamp('2009-05-01 08:00:00','yyyy-mm-dd hh24:mi:ss')
13 from dual
14 /
Tabel is aangemaakt.
Subtracting one timestamp from another, leads to an INTERVAL datatype:
从另一个中减去一个时间戳，得到一个 INTERVAL 数据类型：
SQL> select start_time
2 , end_time
3 , end_time - start_time time_difference
4 from mytable
5 /
START_TIME END_TIME TIME_DIFFERENCE
------------------------------ ------------------------------ ------------------------------
01-05-09 12:34:56,000000000 01-05-09 23:45:01,000000000 +000000000 11:10:05.000000000
01-05-09 23:45:01,000000000 02-05-09 01:23:45,000000000 +000000000 01:38:44.000000000
01-05-09 07:00:00,000000000 01-05-09 08:00:00,000000000 +000000000 01:00:00.000000000
3 rijen zijn geselecteerd.
And INTERVAL datatypes cannot be summed. It's an annoying restriction:
并且 INTERVAL 数据类型不能相加。这是一个恼人的限制：
SQL> select sum(end_time - start_time)
2 from mytable
3 /
select sum(end_time - start_time)
*
FOUT in regel 1:
.ORA-00932: inconsistente gegevenstypen: NUMBER verwacht, INTERVAL DAY TO SECOND gekregen
To circumvent this restriction, you can convert and calculate with the number of seconds, like this:
为了规避此限制，您可以使用秒数进行转换和计算，如下所示：
SQL> select start_time
2 , end_time
3 , trunc(end_time) - trunc(start_time) days_difference
4 , to_number(to_char(end_time,'sssss')) - to_number(to_char(start_time,'sssss')) seconds_difference
5 from mytable
6 /
START_TIME END_TIME DAYS_DIFFERENCE SECONDS_DIFFERENCE
------------------------------ ------------------------------ --------------- ------------------
01-05-09 12:34:56,000000000 01-05-09 23:45:01,000000000 0 40205
01-05-09 23:45:01,000000000 02-05-09 01:23:45,000000000 1 -80476
01-05-09 07:00:00,000000000 01-05-09 08:00:00,000000000 0 3600
3 rijen zijn geselecteerd.
And then they are normal NUMBERs that can be summed
然后它们是可以相加的正常数字
SQL> select sum
2 ( 86400 * (trunc(end_time) - trunc(start_time))
3 + to_number(to_char(end_time,'sssss')) - to_number(to_char(start_time,'sssss'))
4 ) total_time_difference
5 from mytable
6 /
TOTAL_TIME_DIFFERENCE
---------------------
49729
1 rij is geselecteerd.
And if you wish, you can convert this number back to an INTERVAL:
如果您愿意，可以将此数字转换回 INTERVAL：
SQL> select numtodsinterval
2 ( sum
3 ( 86400 * (trunc(end_time) - trunc(start_time))
4 + to_number(to_char(end_time,'sssss')) - to_number(to_char(start_time,'sssss'))
5 )
6 , 'second'
7 ) time_difference
8 from mytable
9 /
TIME_DIFFERENCE
------------------------------
+000000000 13:48:49.000000000
1 rij is geselecteerd.
Regards, Rob.
问候，罗布。
回答by Waldo
This method for Oracle is simple, a bit of a hack though:
Oracle 的这种方法很简单，但有点小技巧：
select sum((end_timestamp+0) - (start_timestamp+0))
from your_table
Result is a NUMBER of days (with fractional part for hours, minutes and you know).
结果是天数（小数部分为小时，分钟，你知道）。
I don't know what timestamp + 0 does exactly; maybe the ANSI timestamp gets converted to Oracle's earlier timestamp type that allows simple arithmetic.
我不知道时间戳 + 0 到底是做什么的；也许 ANSI 时间戳被转换为 Oracle 早期的时间戳类型，允许简单的算术。
回答by Andomar
EDIT: Added trunc() before summing based on Rob van Wijk's excellent reply.
编辑：根据 Rob van Wijk 的出色回复，在求和之前添加了 trunc()。
To find the duration per row:
要查找每行的持续时间：
select
end_date-start_date as DurationDays,
(end_date-start_date)*24 as DurationHours,
(end_date-start_date)*24*60 as DurationMinutes,
(end_date-start_date)*24*60*60 as DurationSeconds
from your_table
To find the total duration:
要查找总持续时间：
select
sum(trunc(end_date-start_date)) as TotalDurationDays
from your_table
To do both in one query:
要在一个查询中同时执行这两项操作：
select
end_date-start_date as DurationDays,
(select sum(trunc(end_date-start_date)) from your_table) as TotalDurationDays
from your_table
回答by Sevas
You could use this query (it works on an Oracle DB at least):
您可以使用此查询（它至少适用于 Oracle DB）：
select sum(end_date - start_date) from your_table
