i need insert video duration to table In minutes
I have a 1:30:00 video I need to convert it to minutes 90 min
BEGIN
DECLARE videTimeSum,channel INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR Select sum(videotime) - COALESCE(sum( TIMESTAMPDIFF(Hour,datestart, now() ) ),0) as videot,channelnumber from videos where flag='0' and channelnumber in (Select channelnumber from channels where flag='0') group by channelnumber order by videot asc limit 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
If(New.channelnumber is NULL or New.channelnumber="")
then
SET @channel=(Select channelnumber from channels2 where flag='0' and channelnumber not in (Select channelnumber from videos where dateend is NULL)
UNION ALL
Select channelnumber from channels where flag='0' and channelnumber not in (Select channelnumber from videos where dateend is NULL) limit 1);
IF(@channel is not NULL and @channel <>'')
then
SET New.channelnumber=@channel;
SET New.playdate=now() + INTERVAL 1 MINUTE;
SET New.enddate=now() + INTERVAL New.videotime MINUTE;
ELSE
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO videTimeSum,channel;
IF done THEN
LEAVE read_loop;
ELSE
SET New.channelnumber=channel;
SET @videTimeSum=videTimeSum;
SET New.playdate = now() + INTERVAL @videTimeSum MINUTE;
SET New.enddate = now() + INTERVAL (@videTimeSum + New.videotime) MINUTE;
END IF;
END LOOP;
CLOSE cur1;
END IF;
END IF;
END
phpmyadmin Triggers sql
You need to sign in to view this answers
Leave feedback about this