mysql从一个连续时间段的表中读取缺少数据

Question

数据库有一个表 想从这个表中某一天内连续时间段比如recevietime字段下查询缺少哪分钟的数据

Answer 1

1.先建个表用于存放 某天的所有分钟

-- Create table
create table T_TEST
(
  receivetime DATE,
  id          INTEGER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80
    next 1
    minextents 1
    maxextents unlimited
  );
-- 插入某一天   以2018-03-23为例
insert into  T_TEST (receivetime,id)
SELECT TO_DATE('2018-03-23', 'YYYY-MM-DD') + (ROWNUM - 1) / (24 * 60)   as time_mm,ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 24*60

2.用表直接联查比对

---测试   删除t_demo中的几个时间   用t_demo(就是相当于你发出的那个业务表 MHAPP.ELE01_AW0S)

 SELECT * from t_test t left join t_demo d 
 
 on  to_char(t.receivetime, 'YYYY-MM-DD hh24:mi') =  to_char(d.receivetime, 'YYYY-MM-DD hh24:mi')
 
 where  to_char(d.receivetime, 'YYYY-MM-DD hh24:mi') is null