How to update the status automatically after 5 days?

Question

I have sent_date and status column in my database table.currently status of the post is = Sent/Received. All I want to change the status = Aborted after 5days automatically.

What I tried:

CREATE EVENT reset ON SCHEDULE EVERY 1 day DO
    update barter_proposals 
       set proposal_status="Aborted"
     WHERE `sent_date`>=DATE_ADD( CURDATE(), INTERVAL 1 day )
       AND proposal_status = "Sent/Received"---------Not worked



CREATE EVENT rot ON SCHEDULE EVERY 1 day DO 
    update barter_proposals 
       set proposal_status="Aborted"
     WHERE DATE_ADD(sent_date, INTERVAL 1 day )>=NOW()
       AND proposal_status = "Sent/Received"---------Not worked



CREATE EVENT rot ON SCHEDULE EVERY 1 day DO 
    update barter_proposals 
       set proposal_status="Aborted"
     WHERE sent_date=CURDATE()
       AND proposal_status = "Sent/Received"-----------Not Worked

How can I update the status automatically after 5 days? sent_date can be anything.

If sent_date is 26/03/2018 then on 01/04/2018, the status should update to Aborted automatically. How can I write the logic?

Please confirm that event scheduler is running see here for detail https://stackoverflow.com/questions/16767923/mysql-event-not-working — P.Salmon, Mar 28 '18 at 07:02
That's the fourth time you have asked this question. I suggest if you haven't done so already that you backtrack a bit and 1) create a working select statement which 2) you convert to a working update statement and then 3) create an event knowing that the update works as intended. AND what do you mean by sent_date can be anything. Also sample data as text on the question would be useful. — P.Salmon, Mar 28 '18 at 07:13
Couple more comments select datediff('2018-03-26','2018-04-01') = -6, Are your dates really stored as date fields? — P.Salmon, Mar 28 '18 at 07:37
@salmon...i used sent_date=DATE(CURDATE()-5),it is giving me the result.. — chetan kambli, Mar 28 '18 at 10:00

score 0 · Accepted Answer · answered Mar 28 '18 at 05:22

0

try CURDATE()-5

CREATE EVENT rot ON SCHEDULE EVERY 1 day DO 
    update barter_proposals 
       set proposal_status="Aborted"
     WHERE sent_date=DATE(CURDATE()-5)
       AND proposal_status = "Sent/Received"

answered Mar 28 '18 at 05:22

Mohideen bin Mohammed

18,813
10
112
118

@Mohideen..IT DINT WORKED FOR ME – chetan kambli Mar 28 '18 at 05:31
its scheduler check whether you have record -5 days from today – Mohideen bin Mohammed Mar 28 '18 at 05:32
SELECT DATE(CURDATE()-5); my output for this, `2018-03-23` so you're record should be 23rd march to update – Mohideen bin Mohammed Mar 28 '18 at 05:54
@mohideen...i want in other way...if date is 26/03/2018,then status should change to aborted on 01/04/2018 – chetan kambli Mar 28 '18 at 05:57
So for tomorrows 29/03/2018,it will display 28/03/2018 (today)entries aborted?or any change is required? – chetan kambli Mar 28 '18 at 09:16
1

so you want to do -4 not -5.. depends on need change date-interval.. as per your need it may be bw 4,5,6 – Mohideen bin Mohammed Mar 28 '18 at 12:24
hi sir...for todays that ie. 4/2/2018(system date),SELECT DATE(CURDATE()-5) shows null in MYSql..why it is so? – chetan kambli Apr 02 '18 at 04:35
you have to change format... default format is YYYY-MM-DD if you want MM/DD/YYYY you have to change it accordingly – Mohideen bin Mohammed Apr 02 '18 at 05:05
sir,but for 3/28/2018(28th march),it displays the date then why not for 4/2/2018(2nd april)? – chetan kambli Apr 02 '18 at 05:34

How to update the status automatically after 5 days?

1 Answers1