CODEWITHSUNDEEP
mysqlsql
Bazinga777
Bazinga777

Reputation: 5281

Loop through mysql table to find a missing day in the table

I am uploading csv files into a mysql table.

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

I want to run a loop that will check if timestamp exists between two time periods say 7:59:59 and 8:59:59, it should append the value of the previous timestamp into the table namely 

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

Upvotes: 0

Views: 39

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Edit: solution for a gap of 30 minutes:

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

Solution for a gap of 1 day:

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

(I added some additional sample data for illustrative purposes)

Upvotes: 1

Related Questions