How can I update value every 20 and 30 minutes?

user_13022619 0
Viewed 361

I have a table ‘comenzi’, each order has a value for in progress, and another for ready and data. Each order when it is created has the values for in progress and ready equal to 0. I want to update the values for in progress and ready with 1 min ,after 20 min, then 30 min after the order has been done, the value from data that has the form 2020-05-25 09:41:00.

I need a way to update these values, a function or an event in database.

events mysql
1 Answers

I was dealing with something using events just yesterday on a pet project. You could use a similar approach.

This code uses a creation column, which is just a TIMESTAMP column with a default value of CURRENT_TIMESTAMP (because I don't have your data) and a value column (default value 0), which goes from 1 - 2 (you can modify this code to suit your needs).

The SET value uses CASEs to check how long ago the order was created. Then it updates the value accordingly. You can change up the event to update whichever columns / time cases you'll need.

CREATE DEFINER =`root`@`localhost` EVENT `progressEvent`
  ON SCHEDULE EVERY 1 MINUTE STARTS '2020-09-01 00:00:00'
  ON COMPLETION PRESERVE ENABLE DO UPDATE comenzi
SET `value` = (
  CASE WHEN (creation < (CURRENT_TIMESTAMP - INTERVAL 30 MINUTE))
  THEN 2
  CASE WHEN (creation < (CURRENT_TIMESTAMP - INTERVAL 20 MINUTE))
  THEN 1
  END)
WHERE value < 2

Edit: Changed it up so that it checks every minute and only 20/ 30 minutes.

user_83593160
Related