When dealing with temporal data, one of the more common (and complex) tasks involves performing addition and subtraction operations on date and time values. Consider, for example, the simple task of calculating a date 91 days hence. Usually, in order to do this with any degree of precision, you need to factor in a number of different variables: the month you're in, the number of days in that month, the number of days in the months following, whether or not the current year is a leap year, and so on.

Writing code to perform such calculations quickly becomes both tedious and complicated. What is really needed in such situations is a date API that supports such date arithmetic, one that takes care of the numerous minor adjustments that have to be made when adding and subtracting intervals to date and time values.

Fortunately, MySQL comes with just such an API, in the form of six functions designed specifically to perform calculations on date and time values. Here they are:

TO_DAYS() - calculates the day number corresponding to a specific date

FROM_DAYS() - calculates the date corresponding to a day number

DATE_ADD() - adds a specified interval to a date and returns a new date

DATE_SUB() - subtracts a specified interval from a date and returns a new date

PERIOD_DIFF() - calculates the difference (in months) between two dates

PERIOD_ADD() - adds an interval (in months) to a date and returns a new date

Let's take a closer look.