There is a special data type 'time' in MySQL.
How would a trigger look if I want my 'time' value to start counting when some state_id changes from 1 to 2? For example:
CREATE TRIGGER log_time AFTER UPDATE ON usr
FOR EACH ROW
BEGIN
IF usr.st_id = 2 THEN
#.... - thats what i dont know
END IF;
END;
It would stop counting when the state_id changes back from 2 to 1.
Instead of starting/stopping the counter (I don't know if that's even possible), you should store the value in 2 different columns (and then substract to get the time when needed)
DELIMITER $$
CREATE TRIGGER log_time AFTER UPDATE ON usr
FOR EACH ROW
BEGIN
IF new.st_id = 2 THEN
UPDATE <table> set <log_start_time> = CURTIME() <where_clause>;
elseif new.st_id = 1 THEN
UPDATE <table> set <log_end_time> = CURTIME() <where_clause>;
END IF;
END;
$$
DELIMITER;
OR in 1 column by storing initial value and then updating it in the trigger
DELIMITER $$
CREATE TRIGGER log_time AFTER UPDATE ON usr
FOR EACH ROW
BEGIN
IF new.st_id = 2 THEN
UPDATE <table> set <logtime> = CURTIME() <where_clause>;
else if new.st_id = 1 THEN
UPDATE <table> set <logtime> = subtime(CURTIME(), select statement to get original value) <where_clause>;
END IF;
END;
$$
DELIMITER;
Okay I'm new to coding and was wondering if this would even be possible and if so what would be the best way to get around this problem
Okay in my database I have a row for current time and a row for duration and I am wanting to find a way so that when the time is the value of T + D it would change a colour (green) ? Or even better if it could be done so if equal to or under 2 mins amber colour and over 2 mins red colour ( kind of like a traffic light idea)
Hope this makes sense
T | D
---------------
22:50 | 6 (mins)
At 22:56 this would change colour on website
Thank You
