在sql查询中计算持续时间的总和
答
你列的数据类型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.
减去另一个时间戳,导致间隔数据类型:
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.
和间隔时间数据类型不能相加。这是一个恼人的限制:
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
为了规避这个限制,你可以转换和计算与秒数,这样的:
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.
然后,它们是可以概括正规数
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.
如果你愿意,你可以这个数字转换回间隔:
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。
答
你可以(至少它的工作原理上的Oracle DB)使用此查询:
select sum(end_date - start_date) from your_table
答
编辑:前加入TRUNC()求和基于罗布面包车Wijk出色的答复。
要找到每行时间:
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
要查找的总持续时间:
select
sum(trunc(end_date-start_date)) as TotalDurationDays
from your_table
要在一个查询一举两得:
select
end_date-start_date as DurationDays,
(select sum(trunc(end_date-start_date)) from your_table) as TotalDurationDays
from your_table
答
这对Oracle方法简单,一个黑客攻击的一位,但:
select sum((end_timestamp+0) - (start_timestamp+0))
from your_table
结果是天的NUMBER(有小数部分的小时,分钟和你知道的)。
我不知道什么时间戳+ 0确实;也许ANSI时间戳被转换为Oracle的早期时间戳类型,允许简单的算术。
当我运行这个查询它说,不一致的数据类型。我使用oracle作为数据库。 – 2009-05-21 09:05:23
这很可能意味着您的start_date和end_date字段的类型不同,但如果您说可以计算每行的时间差异,则情况可能并非如此。 – Sevas 2009-05-21 09:16:05
的数据类型是时间戳 – 2009-05-21 09:17:27