CODEWITHSUNDEEP
mysqlevents
dames
dames

Reputation: 1481

how to create an event that finds out if date is greater than a month and returns value

I need an event that subtracts the current date from a specific date entered.

However, I am working with a criteria of 1 month, if the result of the subtraction is greater than 1 months then update a table with value '1'. So it would Be Current_date- receipt_date, and if this is greater than 1 months then update flag to be '1' else '0'.

I want it to run every minute.

Here's my code so far:

CREATE EVENT myevent3
ON SCHEDULE EVERY '1' MINUTE
DO
UPDATE lms.receipt
SET delinquent = (
    CASE WHEN DATE_ADD( receipt_date, INTERVAL 1 MONTH ) < NOW()
    THEN 1
    ELSE 0
    END
);

Upvotes: 0

Views: 78

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

To make your events run, you need to enable the event scheduler using set global event_scheduler=on. That will enable the event scheduler until the next server restart.

If you want it persistent through server restarts, you will need to add this line to your my.cnf in the [mysqld] section;

event_scheduler=ON

(Reference documentation here)

Upvotes: 1

ajreal
ajreal

Reputation: 47321

When you are writing a result, you can't expect you can return a result at the same time.
You can use cronjob consists of two queries that run every minute to replace the event

  1. first to select ID that matched (output a file for example).
  2. then mark the record to 0 (or 1, depend on your calculation)

Upvotes: 0

Related Questions