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

Question

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

the delinquent column in the receipt table returns neither zero or 1 and i dont know why — dames, Mar 27 '12 at 18:03
Did you enable the scheduler with `set global event_scheduler=on`? — Joachim Isaksson, Mar 27 '12 at 18:05
wow that was the problem, but i enabled it some days ago, tell what causes it to be disabled?, AND thanks a billion — dames, Mar 27 '12 at 18:11
Added a proper answer with the solution and some info how to make the change persistent. — Joachim Isaksson, Mar 27 '12 at 18:22

score 1 · Accepted Answer · answered Mar 27 '12 at 18:20

1

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)

answered Mar 27 '12 at 18:20

Joachim Isaksson

176,943
25
281
294

Could you take a look at this question please I am really in need of some help http://stackoverflow.com/questions/10088948/mysql-event-running-yearly-calculations – dames Apr 10 '12 at 13:52

ajreal · Answer 2 · 2012-03-27T18:11:05.887

0

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

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

edited Mar 27 '12 at 18:11

answered Mar 27 '12 at 18:04

ajreal

46,720
11
89
119

Ok but i have no knowledge of cron jobs, and im using mysql, could you guide me please – dames Mar 27 '12 at 18:07
are you using window? If not, here is the basic http://en.wikipedia.org/wiki/Cron , http://www.gnu.org/software/mcron/manual/html_node/Invoking-cron-or-crond.html#Invoking-cron-or-crond – ajreal Mar 27 '12 at 18:09
1

that won't work on windows, you can try http://support.microsoft.com/kb/308569 . here is an example, http://stackoverflow.com/questions/132971/what-is-the-windows-version-of-cron – ajreal Mar 27 '12 at 18:13
K thanks alot although i found the solution but this is super helpful, i wasnt even aware that this feature could be used in this manner, the problem was i didnt enable: set global event_scheduler=on – dames Mar 27 '12 at 18:20

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

2 Answers2