Calculate duration amount in sql query

Question

I have a table with two starting and ending column times. I can calculate the length of time for each row, but I also want to get the total duration. how to do it.

thanks

Answer 1

Your columns are of TIMESTAMP data type, for example:

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 the INTERVAL data type:

 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 data types cannot be summed. This is an annoying limitation:

 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 get around this limitation, you can convert and calculate with the number of seconds, for example:

 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 up

 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 want, you can convert this number back to 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.

Answer 2

EDIT: Added trunc () before summing up a great answer based on Rob van Wake.

To find the duration for a string:

 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 execute both queries:

 select end_date-start_date as DurationDays, (select sum(trunc(end_date-start_date)) from your_table) as TotalDurationDays from your_table 

Answer 3

This method for Oracle is simple, a bit hacked, though:

 select sum((end_timestamp+0) - (start_timestamp+0)) from your_table 

The result is NUMBER days (with a fractional part for several hours, minutes, and you know).

I do not know which timestamp + 0 does exactly; perhaps the ANSI timestamp is being converted to an earlier Oracle time type, which allows simple arithmetic.

Answer 4

You can use this query (it works at least on Oracle DB):

 select sum(end_date - start_date) from your_table