oracle查询两个时间段是否有交集，解决预约时间碰撞问题

殷乾Lakers

已于 2022-04-27 19:43:22 修改

阅读量2.5k

点赞数

分类专栏：数据库文章标签： oracle 数据库

于 2022-04-27 19:21:44 首次发布

本文链接：https://blog.csdn.net/qq_30976013/article/details/124456554

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

抛出问题

有一设备资源，某些时间段已经被预约，现在创建新的预约单，需要判断本次预约的时间是否有碰撞冲突？

1.准备数据

create table TABLE_EQ_APPOINTMENT
(
  eq_id                VARCHAR2(30),
  appointmentStartTime DATE,
  appointmentEndTime   DATE
)
;
-- Add comments to the columns 
comment on column TABLE_EQ_APPOINTMENT.eq_id
  is '设备资源';
comment on column TABLE_EQ_APPOINTMENT.appointmentStartTime 
  is '预约开始时间';
comment on column TABLE_EQ_APPOINTMENT.appointmentEndTime   
  is '预约结束时间';

insert into table_eq_appointment (EQ_ID, APPOINTMENTSTARTTIME, APPOINTMENTENDTIME)
values ('01E03614', to_date('31-03-2022 09:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('31-03-2022 18:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into table_eq_appointment (EQ_ID, APPOINTMENTSTARTTIME, APPOINTMENTENDTIME)
values ('01E03614', to_date('30-03-2022 09:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('30-03-2022 18:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into table_eq_appointment (EQ_ID, APPOINTMENTSTARTTIME, APPOINTMENTENDTIME)
values ('01E03614', to_date('31-03-2022 18:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('31-03-2022 19:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into table_eq_appointment (EQ_ID, APPOINTMENTSTARTTIME, APPOINTMENTENDTIME)
values ('01E03614', to_date('04-04-2022 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('07-04-2022 19:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into table_eq_appointment (EQ_ID, APPOINTMENTSTARTTIME, APPOINTMENTENDTIME)
values ('01E03614', to_date('02-04-2022 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('02-04-2022 11:00:00', 'dd-mm-yyyy hh24:mi:ss'));

查询sql：

   select
        u.*
        from table_eq_appointment u  where
        u.EQ_ID = '01E03614'
        and  u.appointmentendtime >to_date（'2022-04-02'） and  u.appointmentstarttime <to_date（'2022-04-07' ）

1.全量数据
在这里插入图片描述
2.查询碰撞时间数据（开始时间 start 结束时间 end）

方法一:根据碰撞的三种情况

1.在已预约时间前面    start<appointmentStartTime<end<appointmentEndTime
2.在已预约时间内    appointmentStartTime<start<end<appointmentEndTime
3.在已预约时间后面    appointmentStartTime<start<appointmentEndTime<end

SELECT * FROM table_eq_appointment 
WHERE
    (appointmentStartTime>= start  AND appointmentEndTime<= end)
    OR (appointmentStartTime<= start    AND appointmentEndTime>= end)
    OR (appointmentStartTime>= start    AND appointmentEndTime<= end)

方法二:根据不碰撞的两种情况，查询非不碰撞时间，即碰撞

1.在已预约时间前面    start<end<appointmentStartTime<appointmentEndTime
2.在已预约时间后面    appointmentStartTime<appointmentEndTime<start<end

SELECT * FROM table_eq_appointment u
WHERE NOT (u.appointmentendtime <start or u.appointmentstarttime >end)

方法三:根据碰撞移动

在这里插入图片描述

1.在已预约时间前面    start<appointmentStartTime<end<appointmentEndTime
2.在已预约时间内    appointmentStartTime<start<end<appointmentEndTime
3.在已预约时间后面    appointmentStartTime<start<appointmentEndTime<end

三种情况组合起来就是,需要同时满足:
appointmentstarttime < end  and start<appointmentEndTime

SELECT * FROM table_eq_appointment u
WHERE  u.appointmentendtime >start and u.appointmentstarttime < end