计算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.

从一个时间戳减去另一个时间戳会导致一个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.00000000001-05-09 23:45:01,000000000    02-05-09 01:23:45,000000000    +000000000 01:38:44.00000000001-05-09 07:00:00,000000000    01-05-09 08:00:00,000000000    +000000000 01:00:00.0000000003 rijen zijn geselecteerd.

并且不能对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

为了规避此限制，您可以使用秒数进行转换和计算，如下所示：

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   4020501-05-09 23:45:01,000000000    02-05-09 01:23:45,000000000       1  -8047601-05-09 07:00:00,000000000    01-05-09 08:00:00,000000000       0    36003 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---------------------     497291 rij is geselecteerd.

并且，如果您愿意，可以将此数字转换回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.0000000001 rij is geselecteerd.

问候，罗布。