Database Reference
In-Depth Information
DATE_ADD()
is a very useful function so let's look at some more examples using it.
First, let's redo the previous example to use
DATE_ADD()
instead of
DATE_SUB()
. You
would enter it like this:
UPDATE
humans
SET
membership_expiration
=
DATE_ADD
(
membership_expiration
,
INTERVAL
-
1
YEAR
)
WHERE
CONCAT
(
name_first
,
SPACE
(
1
),
name_last
) =
'Melissa Lee'
;
This is exactly the same as the previous example, except that we're using
DATE_ADD()
and we changed the count of the interval to a negative number to indicate that one year
should be subtracted and not added, despite the name of thefunction.
Let's look at another example with
DATE_ADD()
. Suppose one of the members of our
site recorded a bird sighting in the
bird_sightings
table, but for some reason the day
and time is off. She lets us know that the entry in
time_seen
should be set to one day
and two hours later. After we have determined the
sighting_id
, we can execute this
SQL statement to update the date and time:
UPDATE
bird_sightings
SET
time_seen
=
DATE_ADD
(
time_seen
,
INTERVAL
'1 2'
DAY_HOUR
)
WHERE
sighting_id
=
16
;
In this example, the argument for the interval count is a combination of two intervals,
DAY_HOUR
for both
DAY
and
HOUR
. We list the counts in the same order, and put them
within quotes. If we want to subtract the intervals (i.e., one day and two hours earlier), we
would put a negative sign within the quotes before one of the values. Incidentally, you
can't do a combination of subtracting and adding within the same
DATE_ADD()
. You'd
have to do either two passes at the column, or embed one call within the other.
Table11-1
lists other acceptable combined intervals.
When we use
DATE_ADD()
and similar functions to have MySQL calculate a new date
or time, it goes through a process behind the scenes to determine the new result that is re-
quested. Basically, it counts the number of seconds between dates and times, and then re-
turns the new date and time. There may be situations in which you want to determine the
method of those calculations, when you want more control over those calculations. For
those situations,there are the
TIME_TO_SEC()
and
SEC_TO_TIME()
functions.
The
TIME_TO_SEC()
functionconverts a time to seconds so that a calculation may be
performed easily. If you give it a date and time value, it uses only the time portion. Let's
look at a very simple example of this to see what the results from it mean:
SELECT TIME(NOW()),
TIME_TO_SEC(NOW()),
