drop procedure if exists findClosestTimeStamp;
delimiter $$
create procedure findClosestTimeStamp()
begin
declare tt1 time;
declare dt1 date;
declare view1 varchar(30);
declare done int default false;
declare curComm cursor for
select * from t1; -- This is the query used by the cursor.
declare continue handler for not found -- This handler will be executed if no row is found in the cursor (for example, if all rows have been read).
set done = true;
open curComm;
loop_comm: loop
fetch curComm into dt1,tt1, view1;
if done then
leave loop_comm;
end if;
SELECT * FROM logs
where tt <= tt1 -interval 1 minute;
end loop;
close curComm;
end $$
delimiter ;
call findClosestTimeStamp();
Table t1 has
dt tt view
2020-06-26 16:47:03 TouchDisabled
2020-06-26 07:58:46 Toucbled
2020-06-26 07:58:48 Touchsabled
2020-06-26 07:58:49 TouchUpled
Table logs has
AppName Button dt tt
App1 Button1 2020-06-26 07:45:37
App2 Button2 2020-06-26 07:45:42
App3 Button3 2020-06-26 07:45:44
I am trying to fetch data which occur one minute before the time attribute pointed by cursor in table t1 from the logs table. I dont understand why this is code is printing same value 3 times .and also not doing what I am trying to do!!
edit 1:
when cursor is pointing on the first row of table t1 I will use this tables time into the variable tt1 ,Now using this tt1 time I want to fetch the data in logs table. The data to be fetched should be betwwen tt1-1 minute to tt1
example:
if I take row 1 time 16:47:03 so it should output the logs which are having tt values between 16:46:03 to 16:47:03. I hope this clears question more!!
