CODEWITHSUNDEEP
mysqlstored-proceduresloops
Fotakis
Fotakis

Reputation: 118

mysql variables and loop in stored procedure

I 'm facing a problem when trying to use a timestamp variable in stored procedure's loop. In the following, gtime is a TIMESTAMP input variable and samples is an INTEGER input variable

...
DECLARE t TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE i,step INT DEFAULT 0;

SET t = gtime;

myLabel: LOOP
    IF i = samples THEN
        LEAVE myLabel;
    END IF;

    SELECT a, b FROM myTable 
        WHERE myField = 1
        AND g_time > t
        AND g_time < '2011-09-06 00:00:09';

        SET i = i + 1;      
        SET t = t + 1500;   

    ITERATE myLabel;
END LOOP myLabel;
...

While the i change its value properly and the loop is executed for "samples" times, the t is not affected and thus the SELECT statement does not work well. The t variable has always its original value.

Any ideas why the t variable remains the same? I tried using while statement as well but the problem remains the same. Is it forbidden to use variables in SELECT that are changing withing the loop ?

Upvotes: 1

Views: 4153

Answers (1)

eggyal
eggyal

Reputation: 126035

You can't add an integer 1500 to a TIMESTAMP. To perform date arithmetic you need to use INTERVAL:

SET t = t + INTERVAL 25 MINUTE;

Upvotes: 1

Related Questions