1) Row 3 columns: start, finish and status.

2) The start column stores the start date with the datetime type.

3) The finish column stores the end date of the event with the timestamp type.

4) The status column stores status in the text type.

How can you make it so that if the real time coincides with the date of the finish column, it is a complete match. That any text, whatever would be in the status column, changed to the desired one? For example, if it was "Works", then if it matches, it will automatically change to the text "Disabled".

    2 answers 2

    Complicated. Because it is necessary to cause an update every second ... As an option, you can make an event that will run every second, but this is stupid.

    It is better to update all those who have a date either only less than the current or between between the current and the previous check once every N minutes (again for the event). Well, it is desirable to check the status.

    The event is created like this:

    CREATE EVENT `new_event` ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ON COMPLETION NOT PRESERVE ENABLE COMMENT '' DO UPDATE table_name set status = 'finished' where finish between DATE_SUB(now(),INTERVAL 2 MINUTE) and now() and status != 'finished' ;

    This will create an event that will be executed every minute, and which will set the status to complete for all lines that have finish fall within the last 2 minutes (2 minutes, just to not accidentally miss a line due to the brakes)

    Naturally on the finish would not be bad to hang the index.

    Ps: the status is better to do all the same enum-ohm probably, and not text ...

       UPDATE table SET status='Отключено' WHERE finish = NOW()

      Although, to get into the current time - you need to try, most likely you need:

       ... WHERE finish <= NOW()