Home arrow MySQL arrow Page 7 - Date Arithmetic With MySQL

Lather, Rinse, Repeat - MySQL

When dealing with date and time values, one of the more common (and complex) tasks involves performing addition and subtraction operations on these values. However, with MySQL's powerful date and time API taking care of all the minor adjustments for you, manipulating date and time data is no longer the tedious and time-consuming process it used to be. Find out why, inside.

  1. Date Arithmetic With MySQL
  2. When Two And Two Don't Make Four
  3. Counting Down
  4. The Number Game
  5. Artificial Intelligence
  6. A Short Interval
  7. Lather, Rinse, Repeat
  8. Code Poet
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 35
July 03, 2003

print this article



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 '',
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.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: