How to get only the time of 9:00 to 21:00 between two dates

I have two timestamps e.g '20-Nov-2010 20:11:22' started and ended. Now I want to calculate the time between 9:00 to 21:00 which is 12 hours.

The input will be two dates like '10-Nov-2010' and '20-Nov-2010' start date and end date

componentid   starttime               endtime                result  
3             13-Nov-2010 10:00:00    13-Nov-2010 21:00:00    11:00  hours  
5             14-Nov-2010 09:30:00    14-No开发者_StackOverflow中文版v-2010 22:00:00    11:30  and
3             15-Nov-2010 08:20:00    15-Nov-2010 20:00:00    11:00  minutes
4             16-Nov-2010 08:00:00    16-Nov-2010 23:00:00    12:00
                                                          sum 45:30

Now from examples I only want the hours and minutes in between 9:00 and 21:00 the time which comes in this range from 10-Nov-2010 and 20-Nov-2010. I don't know how to do that in Oracle SQL - can you please explain how to do it?

---

This is for the final sum

select
    trunc(Mi/60) || ':' || right('0' || mod(Mi,60), 2) Total
from
(
    select sum
    (
        (
        case when endtime-trunc(endtime) > 21.0/24 then 21.0/24 else endtime-trunc(endtime) end
        -
        case when starttime-trunc(starttime) < 9.0/24 then 9.0/24 else starttime-trunc(starttime) end
        ) * 24 * 60
    ) Mi
    from tbl
    where starttime >= to_date('10-Nov-2010')
      and endtime < to_date('20-Nov-2010') + 1
) M

---

How would you do it by hand?

• You need to determine the minimum (earlier) of the end time on the day and 21:00 on the day to establish the end point.
• You need to determine the maximum (later) of the start time on the day and 09:00 on the day to establish the start point.
• The difference between these two values is the time you want.

Reducing that to SQL requires a knowledge of the time-manipulation functions for Oracle - this is one of the areas where each DBMS has its own set of idiosyncratic functions and rules and what works on one will not necessarily work on any other.

---

Try this:

SELECT  componentid,
    starttime,
    endtime,
    (
     CASE WHEN (endtime> TRUNC(endtime+ 21/24)) THEN TRUNC(endtime+ 21/24)  ELSE endtime  END - 
     CASE WHEN (starttime< TRUNC(endtime+ 9/24)) THEN TRUNC(starttime+ 9/24)   ELSE starttime END
    )*24  result    
  FROM <YOURTABLE>

---

In the interest of providing yet another way of doing time arithmetic, I'll use a view with interval arithmetic. (PostgreSQL, not Oracle.)

CREATE VIEW experimental_time_diffs AS 
 SELECT component_id, start_time, end_time, 
   case when cast(end_time as time) > '21:00'
        then date_trunc('day', end_time) + interval '21' hour else end_time end - 
   case when cast(start_time as time) < '9:00'
        then date_trunc('day', start_time) + interval '9' hour else start_time end
   AS adj_elapsed_time
   FROM your_table;

And you can sum on the view's column "adj_elapsed_time".

select sum(adj_elapsed_time) as total_elapsed_time 
from experimental_time_diffs;