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