Trigger – If time between?

Posted on

Question :

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?

Answer :

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')
);

After inserting:

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.

Leave a Reply

Your email address will not be published.