Lather, Rinse, Repeat - MySQL

Now that you've (hopefully) understood how MySQL's date calculation functions work, it's time to illustrate them with an example. I'll do this in the context of a real-world application, a task scheduler which supports recurring tasks. Without the various date and time arithmetic functions discussed in this tutorial, building such an application is a time-consuming process; with them, it's a snap.

The requirements of this application are fairly basic:

1. It should allow users to enter tasks, task descriptions and email addresses.

2. It should support recurring daily, monthly or yearly tasks.

3. The recurrence interval should be user-defined.

4. When a task becomes due, a message containing the description should be emailed to the corresponding email address.

Let's begin by building a simple table to store our tasks:

mysql> CREATE TABLE `tasks` (

-> `id` smallint(6) NOT NULL auto_increment,

-> `msg` varchar(255) NOT NULL default '',

-> `type` set('D','M','Y') NOT NULL default '',

-> `interval` mediumint(9) NOT NULL default '0',

-> `date` date NOT NULL default '0000-00-00',

-> `owner` varchar(255) NOT NULL default '', 

-> PRIMARY KEY (id)

-> ) TYPE=MyISAM;

Query OK, 0 rows affected (0.02 sec)

mysql> DESCRIBE tasks;

+----------+------------------+------+-----+------------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+------------------+------+-----+------------+----------------+

| id | smallint(6) | | PRI | NULL | auto_increment |

| msg | varchar(255) | | | | |

| type | set('D','M','Y') | | | | |

| interval | mediumint(9) | | | 0 | |

| date | date | | | 0000-00-00 | |

| owner | varchar(255) | | | | |

+----------+------------------+------+-----+------------+----------------+

6 rows in set (0.00 sec)

Here's a quick explanation of the various fields in this table:

1. The "id" column stores a unique identifier for each task. This identifier is created by MySQL by automatically incrementing the identifier of the previous record.

2. The "msg" column stores a message describing the task.

3. The "type" column specifies the type of recurrence, whether daily (D), monthly (M) or yearly (Y).

4. The "interval" column specifies the interval between each recurrence of the task.

5. The "date" column specifies the date on which the task will run next.

6. The "owner" column specifies the email address to which the task notification should be sent.

It is important to note that in the above structure, a recurrence type of "D" and a recurrence interval of 0 would imply that a task would only execute once, on the date specified.

In order to better understand how this works, consider inserting some data into this table, as follows:

mysql> INSERT INTO tasks (`id`, `msg`, `type`, `interval`, `date`, 

mysql> `owner`)

VALUES ('', 'Run once', 'D', 0, '2003-06-25', 'webmaster@domain'); Query OK, 
1 row affected (0.00 sec)

mysql> INSERT INTO tasks (`id`, `msg`, `type`, `interval`, `date`, 

mysql> `owner`)

VALUES ('', 'Run once daily', 'D', 1, '2003-06-25', 'some.user@some.domain.net'); 


Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tasks (`id`, `msg`, `type`, `interval`, `date`, 

mysql> `owner`)

VALUES ('', 'Run once every 3 days', 'D', 3, '2003-06-29', 'john@where.am.i'); 


Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tasks (`id`, `msg`, `type`, `interval`, `date`, 

mysql> `owner`)

VALUES ('', 'Run once every 2 months', 'M', 2, '2003-06-30', 'user@domain'); 


Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tasks (`id`, `msg`, `type`, `interval`, `date`, 

mysql> `owner`)

VALUES ('', 'Run once every year', 'Y', 1, '2003-07-01', 
'nobody@some.free.mail.service.com'); 


Query OK, 1 row affected (0.00 sec)

With this structure in place, it's pretty simple to write some code to parse this table on a daily basis and use MySQL's date functions to calculate which tasks to execute. Let's look at that next.