I wish to use a mysql trigger that will update a column called peak to 0 or 1 based on if the CURTIME() is between 08:01 & 11:59.
Can anyone help with this trigger?
As an alternative to triggers, if time was just base on a column value like a time that defaulted to CURTIME, a generated expression could be used:
create table dt ( t time, peak bool generated always as (t between '08:01' and '11:59') );
insert into dt(t) values ('8:00'),('8:01'), ('8:02'),('11:58'),('11:59'),('12:00');
The results are:
select * from dt; | t | peak | | -------- | ---- | | 08:00:00 | 0 | | 08:01:00 | 1 | | 08:02:00 | 1 | | 11:58:00 | 1 | | 11:59:00 | 1 | | 12:00:00 | 0 |
Like a normal column, peak can appear in indexes.
You need something like that:
BEGIN IF CURTIME() BETWEEN '08:00:00' AND '11:59:59' THEN UPDATE ... END IF END
This trigger will be activated each time the event occure but it will do something only if current time is within the given range and will do nothing otherwise.