MySQL – Scheduling an Event Every Day at a Given Time

eventMySQL

I want my event to run everyday at 00:20. I am confused in scheduling of the time. Should I be using the first option or the second one?

Option 1

CREATE EVENT my_event
  ON SCHEDULE
    EVERY 1 DAY
    STARTS '2014-04-30 00:20:00' ON COMPLETION PRESERVE ENABLE 
  DO
    # My query

Option 2

CREATE EVENT my_event
  ON SCHEDULE
    AT ('2014-04-30 00:20:00'+ INTERVAL 1 DAY) ON COMPLETION PRESERVE ENABLE 
  DO
    # My query

Best Answer

Use the first one if you want to execute your event everyday.

The second syntax will execute once.

Option 1: Execute the event every night at 00:20

Option 2: Execute the event once the 2014-05-01 at 00:20

Max.

Related Solutions

Mysql – selecting the month day and year from a transaction table

You will want to use the DATE_FORMAT function in MySQL to format the date for each query.

-- query 1
select date_format(date, '%m-%d') `m-d`,
  sum(amount) Total
from mytable
group by date_format(date, '%m-%d');

See SQL Fiddle with Demo. Gives a result:

|   M-D | TOTAL |
-----------------
| 04-08 |    50 |
| 05-01 |   200 |
| 05-03 |   400 |

-- query 2
select date_format(date, '%Y-%m') `Y-m`,
  sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');

See SQL Fiddle with Demo. Gives the result:

|     Y-M | TOTAL |
-------------------
| 2013-04 |    50 |
| 2013-05 |   600 |

MySQL – Automatically Purging Binary Logs

Seems like expire_logs_days 7 would be a lot simpler.

8.0 is moving toward binlog_expire_logs_seconds.

Best Answer

Related Solutions

Mysql – selecting the month day and year from a transaction table

MySQL – Automatically Purging Binary Logs

Related Question