select rows having time difference less than 2 hour of a single column

Question

Table: TEST

Select rows having time difference less than 2 hour for the same day (group by date).

Here output should be first two rows, because the Time difference of the first two rows (18-JAN-15 01.08.40.000000000 PM - 18-JAN-15 11.21.28.000000000 AM < 2 hour)

NB: compare rows of same date.

OUTPUT:

  CREATE TABLE TEST
  ( "ID" VARCHAR2(20 BYTE), 
    "CAM_TIME" TIMESTAMP (6)
  ) 

Insert into TEST (ID,CAM_TIME) values ('1',to_timestamp('18-JAN-15 11.21.28.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST (ID,CAM_TIME) values ('2',to_timestamp('18-JAN-15 01.08.40.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into TEST (ID,CAM_TIME) values ('3',to_timestamp('23-JAN-15 09.18.40.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST (ID,CAM_TIME) values ('4',to_timestamp('23-JAN-15 04.22.22.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Answer 1

I took a slightly different tack and employed the LAG() and LEAD() analytic functions:

WITH mydata AS (
  SELECT 1 AS id, timestamp '2015-01-15 11:21:28.000' AS cam_time
    FROM dual
   UNION ALL
  SELECT 2 AS id, timestamp '2015-01-15 13:08:40.000' AS cam_time
    FROM dual
   UNION ALL
  SELECT 3 AS id, timestamp '2015-01-23 09:18:40.000' AS cam_time
    FROM dual
   UNION ALL
  SELECT 4 AS id, timestamp '2015-01-23 16:22:22.000' AS cam_time
    FROM dual
)
SELECT id, cam_time FROM (
    SELECT id, cam_time
         , LAG(cam_time) OVER ( PARTITION BY TRUNC(cam_time) ORDER BY cam_time ) AS lag_time
         , LEAD(cam_time) OVER ( PARTITION BY TRUNC(cam_time) ORDER BY cam_time ) AS lead_time
      FROM mydata
) WHERE CAST(lead_time AS DATE) - CAST(cam_time AS DATE) < 1/12
     OR CAST(cam_time AS DATE) - CAST(lag_time AS DATE) < 1/12