Selecting missing entries in MySQL

Question

I have a database which contains data saved every 30 minutes (+/- ~3 seconds). There are about 20 000 records. Now I want to get all the datetimes when there isn't a record saved: For example, I don't want to get 2012-11-22 16:30 as a result because it exists in the database. But I want to get 2012-11-22 16:00 as one because the database doesn't contain an entry with that date.

Remember that the seconds part may vary. Usually it's exactly at the minute but sometimes it can be 2012-05-10 10:00:03 or so.

How do I do such a query?

What do you mean? It's not a routine task that needs to be executed. — MikkoP, Jan 01 '13 at 10:26
@MikkoP- i mean one stored procedure, you will enter 'from' and 'to' date to stored procedure, and it will return missing entries — Fathah Rehman P, Jan 01 '13 at 10:29
from your other question, you seem to be having some trouble with the stored procedure; how about giving my answer a try? — ysth, Jan 04 '13 at 20:30

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

If you're able to use stored procedures, then you can use this stored procedure to generate a range of date-times between the highest and lowest dates in the system.

If you can't be certain about the to-the-minute granularity of your timestamps, then you may need to use seconds as the interval instead of minutes.

A left-join against this table should reveal the dates and times when data hasn't been saved.

edited May 23 '17 at 11:43

Community

1
1

answered Jan 01 '13 at 10:29

Daniel Miladinov

1,582
9
20

MySQL doesn't like it: it says there's syntax errors near `timestamp` on line 13, `declare` on line 14, `repeat` on line 30, `until` on line 46 and `END` on line 49. – MikkoP Jan 01 '13 at 10:40
You probably have to change the delimiter, see example here: http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html – Erik Ekman Jan 01 '13 at 10:47

ysth · Answer 2 · 2013-01-02T00:47:51.140

If you are looking for gaps, an easier query would be to find all times for which the next later time isn't within 30 minutes 6 seconds.

It is possible to do it in a single query for a specific total length of time. The following will check for missing times in a given range using an ad-hoc table of 65536 even 30 minute times from 2010 on (about 3.7 years of times):

select t
    from (select date_add('2010-01-01', interval (a+4*b+16*c+64*d+256*e+1024*f+4096*g+16384*h)*30 minute) t from (select 0 a union select 1 union select 2 union select 3) a, (select 0 b union select 1 union select 2 union select 3) b, (select 0 c union select 1 union select 2 union select 3) c, (select 0 d union select 1 union select 2 union select 3) d, (select 0 e union select 1 union select 2 union select 3) e, (select 0 f union select 1 union select 2 union select 3) f, (select 0 g union select 1 union select 2 union select 3) g, (select 0 h union select 1 union select 2 union select 3) h order by t) ad_hoc_times
left join ( your_table, (select -3 t_adj union select -2 union select -1 union select 0 union select 1 union select 2 union select 3) t_adj )
    on your_timestamp=date_add(t, interval t_adj second)
where t between '2010-07-01' and '2012-07-01'
    and your_table.your_timestamp is null;

(Your timestamp field must be indexed.)

hmm, if the stored procedures, as suggested by others, need a temporary table, that makes them less interesting. — ysth, Jan 02 '13 at 00:47

Fathah Rehman P · Answer 3 · 2013-01-02T04:41:34.790

I created one table to show my stored procedure. Table creation query is given below

CREATE TABLE `testtable1` (
    `id` INT(11) NULL DEFAULT NULL,
    `timecol` DATETIME NULL DEFAULT NULL
)

Table contain data as given below

enter image description here

To meet your requirement i created following stored procedure

DELIMITER $$  
CREATE PROCEDURE proc1(fromtime DATETIME,totime DATETIME)

   BEGIN
      DECLARE a INT Default 1;
      DECLARE temptime DATETIME;
      DECLARE ini,diff,nos int;      
  DECLARE temp1,temp6 datetime;
  drop table if exists mytemptable;
  CREATE TEMPORARY TABLE IF NOT EXISTS mytemptable ( `missing_dates` DATETIME NULL DEFAULT NULL);    

  if(minute(fromtime)>30) then
   set diff=60-(minute(fromtime));       
  else
   set diff=30-(minute(fromtime));       
  end if;
  set temptime=ADDTIME(fromtime,concat('00:',diff,':00'));

  while((unix_timestamp(totime)-unix_timestamp(temptime))>0) DO
  set temp1=SUBTIME(temptime,'00:00:03');     
  set temp6=ADDTIME(temptime,'00:00:03');      

   select count(*) into nos from testtable1 where timecol>=temp1 and timecol<=temp6;
   if(nos=0) then
     insert into mytemptable (missing_dates) values (temptime);
   end if;
  set temptime=ADDTIME(temptime,'00:30:00');
  END WHILE;
 select * from mytemptable;

END $$

To get your required result just call above stored procedure with 'from time' and 'to time'. For example

call proc1('2013-01-01 14:00:00','2013-01-01 17:00:00')

Result is given below

enter image description here

Selecting missing entries in MySQL

3 Answers3