Add two timestamp variable in mysql trigger

Question

I have written my trigger like this

CREATE TRIGGER `update_total_duration` AFTER UPDATE ON `sales_activity`
 FOR EACH ROW thisTrigger:BEGIN  
IF(NEW.activity_id=4) 
    THEN LEAVE thisTrigger;
ELSEIF NOT EXISTS
    (SELECT 1 FROM sales_duration_update WHERE user_id = NEW.user_id AND   date = CURDATE()) 
    THEN INSERT INTO sales_duration_update (user_id,date,total_duration) VALUES (NEW.user_id,CURDATE(),NEW.duration);
ELSE
    //problem is here when total_duration = total_duration + NEW.duration
    UPDATE sales_duration_update SET total_duration = total_duration + NEW.duration WHERE user_id = NEW.user_id AND date = CURDATE();
END IF;

END

Problem is here in code total_duration = total_duration + NEW.duration where both variable are timestamp variable.How can I add two timestamp variables in trigger?

You can show some examples and some test data. [TIMESTAMPADD](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampadd) and [TIMESTAMPDIFF](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff) functions can be useful. — wchiquito, Apr 07 '16 at 17:03
I tried total_duration = TIMESTAMPADD(total_duration + NEW.duration) but its didn't work — Mad Adh, Apr 07 '16 at 18:38

Mad Adh · Answer 1 · 2016-04-11T20:21:26.167

I found the answer.May be will help someone.

CREATE TRIGGER `update_total_duration` AFTER UPDATE ON `sales_activity`
FOR EACH ROW thisTrigger:BEGIN  
IF(NEW.activity_id=4) 
THEN LEAVE thisTrigger;
ELSEIF NOT EXISTS
(SELECT 1 FROM sales_duration_update WHERE user_id = NEW.user_id AND   date     = CURDATE()) 
    THEN 
INSERT INTO sales_duration_update (user_id,date,total_duration) VALUES     (NEW.user_id,CURDATE(),NEW.duration);
ELSE
UPDATE sales_duration_update SET total_duration = ADDTIME(total_duration,     NEW.duration) WHERE user_id = NEW.user_id AND date = CURDATE();
END IF;

Add two timestamp variable in mysql trigger

1 Answers1