CODEWITHSUNDEEP
oracle-databasedatetimeplsqloracle11g
Bishan
Bishan

Reputation: 15740

Get Gap between time range

In WORK_TIME column in my database table (EMP_WORKS), i have records as below.

WORK_TIME

19:03:00
20:00:00
21:02:00
21:54:00
23:04:00
00:02:00

i want to create a database view using these data. for it i need to get Gap between these times as below.

WORK_TIME  GAP

19:03:00  -
20:00:00  00:57:00  (Gap between 19:03:00 and 20:00:00)
21:02:00  01:02:00  (Gap between 20:00:00 and 21:02:00)
21:54:00  00:52:00  (Gap between 21:02:00 and 21:54:00)
23:04:00  01:10:00  (Gap between 21:54:00 and 23:04:00)
00:02:00  00:58:00  (Gap between 23:04:00 and 00:02:00)

How could i do this ?

Upvotes: 2

Views: 847

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27261

According to your desired result, provided in the question, you want to see time interval. And also I suppose that the WORK_TIME column is of date datatype and there is a date part(otherwise there will be a negative result of subtraction (previous value of WORK_TIME from 00.02.00)).

SQL> create table Work_times(
  2    work_time
  3  ) as
  4  (
  5  select to_date('01.01.2012 19:03:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  6  select to_date('01.01.2012 20:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  7  select to_date('01.01.2012 21:02:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  8  select to_date('01.01.2012 21:54:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  9  select to_date('01.01.2012 23:04:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
 10  select to_date('02.01.2012 00:02:00', 'dd.mm.yyyy hh24:mi:ss') from dual
 11  )
 12  /

Table created

SQL> 
SQL> select to_char(t.work_time, 'hh24.mi.ss') work_time
  2         , (t.work_time -
  3            lag(t.work_time) over(order by WORK_TIME)) day(1) to second(0) Res
  4  from work_times t
  5  ;

WORK_TIME RES
--------- -------------------------------------------------------------------------------
19.03.00  
20.00.00  +0 00:57:00
21.02.00  +0 01:02:00
21.54.00  +0 00:52:00
23.04.00  +0 01:10:00
00.02.00  +0 00:58:00

6 rows selected

Upvotes: 1

Ahmad
Ahmad

Reputation: 12737

First you will need to have a primary key in the table containing the DATE/TIME field.

I have set up this demo on SQL Fiddle .. Have a look

I have represented the gap as a factor of hours between the two times. You can manipulate the figure to represent minutes, or days, whatever.

SELECT 
TO_CHAR(A.WORK_TIME,'HH24:MI:SS') WORK_FROM,
TO_CHAR(B.WORK_TIME,'HH24:MI:SS') WORK_TO,
ROUND(24*(B.WORK_TIME-A.WORK_TIME),2) GAP FROM 
  sample A,
  SAMPLE B
WHERE A.ID+1 = B.ID(+)

If your primary key values have difference greater than 1 (gaps within the values of the primary key) then you will need to offset the value dynamically like this:

  SELECT 
TO_CHAR(A.WORK_TIME,'HH24:MI:SS') WORK_FROM,
TO_CHAR(B.WORK_TIME,'HH24:MI:SS') WORK_TO,
ROUND(24*(B.WORK_TIME-A.WORK_TIME),2) GAP FROM 
  sample A,
  SAMPLE B
WHERE  b.ID = (select min(C.ID) from sample c where c.id>A.ID)

SQL Fiddle Screenshot

Upvotes: 3

Peter Lang
Peter Lang

Reputation: 55594

This query will get you the differences in hours:

SELECT
  work_time,
  ( work_time - LAG(work_time) OVER (ORDER BY work_time) ) * 24 AS gap
FROM emp_works

Example on SQL Fiddle returns this:

WORK_TIME                           GAP
November, 07 2012 19:03:00+0000     (null)
November, 07 2012 20:00:00+0000     0.95
November, 07 2012 21:02:00+0000     1.033333333333
November, 07 2012 21:54:00+0000     0.866666666667
November, 07 2012 23:04:00+0000     1.166666666667
November, 08 2012 00:02:00+0000     0.966666666667

Upvotes: 3

Related Questions