Date Arithmetic With MySQL - When Two And Two Don't Make Four
(Page 2 of 8 )
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.
Next: Counting Down >>
More MySQL Articles
More By icarus, (c) Melonfire
/ 34 
