选择单列时间差小于2小时的行 [英] select rows having time difference less than 2 hour of a single column

问题描述

表:TEST

选择在同一天(按日期分组)时差小于2小时的行.

这里的输出应该是前两行，因为前两行的时差(15-JAN-15 01.08.40.000000000 PM-15-JAN-15 11.21.28.000000000 AM< 2 hour)

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.

输出:

  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'));

推荐答案

此自联接查询可以完成此任务:

This self-join query does the job:

SQL提琴

select distinct t1.id, t1.cam_time 
  from test t1 join test t2 on t1.rowid <> t2.rowid  
    and trunc(t1.cam_time) = trunc(t2.cam_time)
  where abs(t1.cam_time-t2.cam_time) <= 2/24
  order by t1.id

如果cam_time是time_stamp类型，则条件应为:

If cam_time is time_stamp type then condition should be:

where t1.cam_time between t2.cam_time - interval '2' Hour 
                      and t2.cam_time + interval '2' Hour

这篇关于选择单列时间差小于2小时的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！