Date and Time Functions - Learning MySQL and MariaDB - page 258

Adding and Subtracting Dates and Time
MySQL and MariaDBinclude several built-in functions that may be used to change a given
date or time. You can use them to change a date to a future one by adding time, or change a
date to a past one by subtracting time. The main functions that do this, or perhaps the most
popular ones,are DATE_ADD() and DATE_SUB() . The syntax for both of these is the
same: the first argument is the date to be modified and the second argument is the amount
of time. The amount of time is presented with thekeyword INTERVAL , followed by a
count of intervals, followed by the date or time factor (e.g., INTERVAL 1 DAY ).
Let's look at an example using DATE_ADD() . Suppose we want to extend the membership
of all of our members who live in the United Kingdom by three months. To do this, we
would enter the following:
UPDATE humans
SET membership_expiration = DATE_ADD ( membership_expiration , INTERVAL
3 MONTH )
WHERE country_id = 'uk'
AND membership_expiration > CURDATE ( );
In this example, we're adding three months to the current membership_expiration ,
but just for members who are in the U.K., but not for those whose membership has already
expired. Notice that we're using a simpler operator, in this case the greater-than sign ( > ), to
compare two day values in the WHERE clause. Notice also how we had to set the member-
ship_expiration column equal to the modified value of itself. Date and time func-
tions don't change the value of columns simply by being executed. You have to use them in
conjunction with other methods for them to affect stored data. For a list of intervals al-
lowed with DATE_ADD() and similar date and time functions, see Table11-1 .
Let's look at another example using DATE_SUB() . Suppose a member named Melissa Lee
renewed her membership for two years, but meant to renew it for only one year. You could
enter the following SQL statement to make that adjustment:
UPDATE humans
SET membership_expiration = DATE_SUB ( membership_expiration , INTERVAL
1 YEAR )
WHERE CONCAT ( name_first , SPACE ( 1 ), name_last ) = 'Melissa Lee' ;
Because there may be more than one Melissa Lee in our database, we should have first de-
termined her human_id and used that in the WHERE clause.