time - How to AUTO update MySQL after timestamp field expierd

Question

How do I automate MySQL Update or Insert upon expire timestamp?

So let say timestamp is 2013-06-30 20:10:00 and I would like to auto update MySQL DB upon passing that date and time so today after 2013-06-03 20:10:00.

I want to update my item, but I will not have browser open with my website, so I think so I need some kind of server trigger?? How do I do that? Many thanks

I have following Query to execute every 1 second and it doesn't work:

CREATE EVENT e_second
    ON SCHEDULE
      EVERY 1 SECOND
    DO
UPDATE online_auctions.auction_status
   SET auction_status = 'ENDED' WHERE TIMESTAMP(auction_end_date) < (select now());

Answer 1

Use can use for that

1. Mysql events (IMHO the best candidate)
2. cron job or Windows Task Scheduler (if you're on Windows platform)

If you go with option 1 you need to create an event

CREATE EVENT myevent 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 
UPDATE myschema.mytable 
   SET mycol = mycol + 1;

Use SHOW PROCESSLIST to check if event scheduler is enabled. If it's ON you should see a process "Daemon" by user "event_scheduler". Use SET GLOBAL event_scheduler = ON; to enable the scheduler if it's currently not enabled. More on configuring event scheduler here.

If you want to see events that you've in your schema

SHOW EVENTS;

UPDATE Your update statement should look like

UPDATE online_auctions 
   SET auction_status = 'ENDED' 
 WHERE auction_end_date < NOW();

Here is SQLFiddle demo