mysql - 循环遍历mysql表以查找表中缺失的一天

Question

我正在将 csv 文件上传到 mysql 表中。

time_stamp,"Phase 1","Phase 2","Phase 3",Total
2014-07-09 07:59:21,8345,8665,5461,22471
2014-07-09 08:59:21,8345,8665,5461,22471
2014-07-10 07:59:57,9349,9750,6550,25649

我想运行一个循环来检查两个时间段(例如 7:59:59 和 8:59:59)之间是否存在时间戳，它应该将前一个时间戳的值附加到表中，即

2014-07-09 08:29:21,8345,8665,5461,22471

Answer 1

编辑:30分钟间隙的解决方案:

select timestampadd(minute, 30, x.time_stamp) as time_stamp,
       x.phase_1,
       x.phase_2,
       x.phase_3,
       x.total
  from tbl x
 cross join tbl y
 where y.time_stamp =
       (select max(z.time_stamp)
          from tbl z
         where timestampdiff(minute, z.time_stamp, x.time_stamp) < 30)
   and timestampdiff(minute, y.time_stamp, x.time_stamp) not between 0 and
       29.99
   and y.time_stamp < (select max(time_stamp) from tbl)

fiddle : http://sqlfiddle.com/#!2/51d84/3/0

1天间隔的解决方案:

insert into tbl
select timestampadd(day, 1, x.time_stamp) as time_stamp,
       x.phase_1,
       x.phase_2,
       x.phase_3,
       x.total
  from tbl x
 cross join tbl y
 where y.time_stamp =
       (select max(z.time_stamp)
          from tbl z
         where cast(z.time_stamp as date) < cast(x.time_stamp as date))
   and cast(y.time_stamp as date) < cast(x.time_stamp as date)
   and cast(timestampadd(day, 1, x.time_stamp) as date) not in
       (select cast(time_stamp as date) from tbl);

fiddle : http://sqlfiddle.com/#!2/ab1c3/2/0

(出于说明目的，我添加了一些额外的示例数据)