DBA.SE question #95325

GtfsTimeAdd.sql

drop function if exists GtfsTimeAdd$$
show create function GtfsTimeAdd$$
create function GtfsTimeAdd(src int(10), seconds float) returns int(10)
deterministic
begin
  set @h = truncate(src/10000, 0);
  set @m = truncate((src-10000*@h)/100, 0);
  set @s = src-10000*@h-100*@m;
  
  set @secs = truncate(3600 * @h + 60 * @m + @s + seconds, 0);

  set @as = mod(@secs, 60);
  set @am = mod(truncate((@secs-@as) / 60, 0), 60);
  set @ah = truncate((@secs-@am*60-@as) / 3600, 0);
  
  return 10000*(@ah)+100*(@am)+(@as);
end$$

delimiter ;

testbug.sql

call testbug();
drop procedure testbug;
delimiter $$

CREATE PROCEDURE `testbug`()
begin

	drop table if exists t1d;
	drop table if exists t1a;
	drop table if exists trip1;
	drop table if exists t2d;
	
	create temporary table t1d as
	select t1.id as t1_iid, t1.trip_type as t1_type
	from stops s1d
	left join stop_times_12 st1d on st1d.stop_iid = s1d.id
	inner join trips_part t1 on t1.id = st1d.trip_iid
	limit 10;
		
	create temporary table t1a as
	select t1.*, st1a.idep as st1a_idep, s1a.id as s1a_iid
	from t1d t1
	left join stop_times_12 st1a on st1a.trip_iid = t1.t1_iid
	left join stops s1a on s1a.id = st1a.stop_iid;

	create temporary table trip1
	select t1.*, cs1.to_stop_iid as cs1_to, GtfsTimeAdd(st1a_idep, cs1.distance) as t1_arr
	from t1a t1
	inner join stop_connections cs1 on cs1.from_stop_iid = t1.s1a_iid
	order by t1_arr asc;
	
	set @sql = "create temporary table t2d as
	select * from (
		select t1.*
		from trip1 t1
		left join stop_times_12 st2d on st2d.idep < GtfsTimeAdd(t1.t1_arr, 45*60)
	) a ";

	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

END$$

delimiter ;