CODEWITHSUNDEEP
oracle-databaseoracle11g
JDro04
JDro04

Reputation: 670

Returning times between other times

I have two tables AVAIL and AVAIL_TIMES. AVAIL contains avail_id, avail_date, open_flag. AVAIL_TIMES contains avail_times_id, Avail_id, Start_Time, End_time. All date and time fields are typed as DATE

If a date is flagged in the avail open_flag column it means that the facility is open for that date, but the times it is open is listed in avail_times. There can be multiple time ranges for a particular day.

I need to return a list of times it is not open for that day.

For Example (one day of many)

Open times for day:

Start_time: 08:00 End_time 10:00
Start_time: 12:00 End_time 14:00
Start_time: 15:00 End_time 17:00

I want it to return something like:

00:00 - 07:59
10:01 - 11:59
14:01 - 14:59
17:01 - 23:59

I think I would be able to work through this with a temporary table and some plsql logic, but ideally this would be a pure sql solution.

Upvotes: 1

Views: 72

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Another Approach. Hope this helps.

SELECT ID,
  START_TME,
  END_TM,
  DIFF_TM
FROM
 --Not part of SQL just to simulate the table data
  (WITH TMP AS
  (SELECT 1 ID,
    TO_DATE('06/27/2017 00:00','mm/dd/yyyy hh24:mi') START_TME,
    TO_DATE('06/27/2017 08:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  UNION ALL
  SELECT 1 ID,
    TO_DATE('06/27/2017 10:00','mm/dd/yyyy hh24:mi') START_TME,
    TO_DATE('06/27/2017 15:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  UNION ALL
  SELECT 1 ID,
    TO_DATE('06/27/2017 16:00','mm/dd/yyyy hh24:mi') START_TME,
    TO_DATE('06/27/2017 17:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  UNION ALL
  SELECT 1 id,
    to_date('06/27/2017 17:00','mm/dd/yyyy hh24:mi') start_tme,
    TO_DATE('06/27/2017 18:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  )
  --SQL start from here
SELECT TMP.*,
  LEAD(START_TME) OVER(PARTITION BY ID ORDER BY 1 DESC) next_st_tm,
  LEAD(END_TM) OVER(PARTITION BY ID ORDER BY 1 DESC) NEXT_EN_TM,
  EXTRACT( HOUR FROM TO_TIMESTAMP(LEAD(START_TME) OVER(PARTITION BY ID ORDER BY 1 DESC),'MM/DD/YYYY HH24:MI'))- EXTRACT(HOUR FROM TO_TIMESTAMP(end_tm,'MM/DD/YYYY HH24:MI')) DIFF_TM
FROM TMP
ORDER BY 1 ,
  2
  )
WHERE DIFF_TM <> 0;

Upvotes: 0

user5683823
user5683823

Reputation:

I am not exactly sure how you want to input the date of interest (I used a bind variable, passed in as a string - but that may not be the right way for you, perhaps you want to join to your other table, etc.) - or the exact output you want. In any case, the query below demonstrates the "core" of the code you need to achieve this kind of output from the inputs.

alter session set nls_date_format='mm/dd/yyyy hh24:mi';

with
     avail_times ( start_time, end_time ) as (
       select to_date('06/20/2017 08:00'), to_date('06/20/2017 10:00') from dual union all
       select to_date('06/20/2017 12:00'), to_date('06/20/2017 14:00') from dual union all
       select to_date('06/20/2017 15:00'), to_date('06/20/2017 17:00') from dual
     )
select   trunc(min(start_time)) as start_time, min(start_time) as end_time
  from   avail_times
  where  trunc(start_time) = to_date(:input_date, 'mm/dd/yyyy')
union all
select   end_time, 
         lead(start_time, 1, trunc(start_time) + 1) over (order by start_time)
  from   avail_times
  where  trunc(end_time) = trunc(start_time)
order by start_time
;

START_TIME        END_TIME
----------------  ----------------
06/20/2017 00:00  06/20/2017 08:00
06/20/2017 10:00  06/20/2017 12:00
06/20/2017 14:00  06/20/2017 15:00
06/20/2017 17:00  06/21/2017 00:00

Upvotes: 1

Related Questions