7

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

Improve this question

4 Answers 4

Reset to default
23

Your columns are of datatype TIMESTAMP, like this:

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:

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:

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:

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+1 Very educating, I assumed that trunc() was Oracle's name for round()
trunc() cuts of the time part of a date/timestamp. Truncating 2009-05-01 23:59:59 leads to 2009-05-01 00:00:00. Rounding leads to 2009-05-02 00:00:00.
Thanks Rob for the Reply. Its working now by implementing your above code. Thanks again........
Five years later, still awesome. This is just what I needed to understand the troubles I was having.
Thanks for the answer, it's working! By the way I am shocked that this is the correct answer to this common problem. Dear Oracle this is disgusting. I want my sum(end_time-start_time)... :(
3

This method for Oracle is simple, a bit of a hack though:

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.

Improve this answer

1 Comment

TIMESTAMP + 0 will implicitly cast the TIMESTAMP data type to a DATE data type (losing the fractional seconds component of the TIMESTAMP) and add zero days to it. You could equivalently write your answer as SELECT SUM( CAST( end_timestamp AS DATE ) - CAST( start_timestamp AS DATE ) ) FROM your_table. Note: if your timestamps have fractional second components then this answer will give an incorrect result (and if they don't have fractional seconds why are you storing them as a TIMESTAMP?).
2

EDIT: Added trunc() before summing based on Rob van Wijk's excellent reply.

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
Improve this answer

3 Comments

when i run this query it says that inconsistent datatypes. I am using oracle as the database.
Can you post the output of "DESCRIBE your_table" ?
Thanks a lot for the reply Andomar....I have solved my issue with Rob's help........thanks a lot
1

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

select sum(end_date - start_date) from your_table
Improve this answer

4 Comments

when i run this query it says that inconsistent datatypes. I am using oracle as the database.
This most likely means that your start_date and end_date fields are of different types, but if you say you can calculate the time difference for each row, this might not be the case.
Can you please paste the SQL that defines the table?
Thanks a lot for the reply Sevas....I have solved my issue with Rob's help........thanks a lot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.