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.
In addition to futzing with days and dates, MySQL also provides you with the ability to perform date arithmetic on specific date and time values, with its DATE_ADD() and DATE_SUB() functions. The syntax of these functions is somewhat more complex than the ones you've seen thus far - here's what it looks like:
DATE_ADD(startDate, INTERVAL period periodType)
DATE_SUB(startDate, INTERVAL period periodType)
In order to better understand this, consider the following example, which adds 1 year to the specified start date and returns the new value:
In order to better understand this, it's necessary to delve a little deeper into the syntax of the DATE_ADD() and DATE_SUB() functions:
1. The first parameter, startDate, is a date or time value in either string ("YYYY-MM-DD HH:MM:SS") or number (YYYYMMDDHHMMSS) format. This parameter specifies the date to use as a base for the calculation.
2. The second parameter consists of three separate components, which together specify the interval which is to be added to (or subtracted from) the first parameter. The components consist of the keyword INTERVAL, followed by the interval period and a keyword providing information on the interval calculation to be performed.
The relationship between the formatting of the interval period and the keyword following it is fixed, and illustrated in the following table:
period (formatted as) periodType --------------------------------------------- SECONDS SECOND MINUTES MINUTE HOURS HOUR DAYS DAY MONTHS MONTH YEARS YEAR "MINUTES:SECONDS" MINUTE_SECOND "HOURS:MINUTES" HOUR_MINUTE "DAYS HOURS" DAY_HOUR "YEARS-MONTHS" YEAR_MONTH "HOURS:MINUTES:SECONDS" HOUR_SECOND "DAYS HOURS:MINUTES" DAY_MINUTE "DAYS HOURS:MINUTES:SECONDS" DAY_SECOND
With this in mind, the examples above should become much clearer. If, for example, you wanted to perform a calculation involving interval units of 1 year, you could format your arguments using the YEAR type, whereas if you wanted to perform date arithmetic with hours and minutes, you could format your arguments using the HOUR_MINUTE or HOUR_SECOND types.
Let's look at a few more examples of this in action.