Database Reference
In-Depth Information
Comparing Dates and Times
We've seen, in a fewexamples in this topic, some ways to compare values containing dates
and times. Several functions are designed specifically for this task. The most straightfor-
wardones are
DATEDIFF()
and
TIMEDIFF()
. With these, you can easily compare two
dates or times. Let's look at some examples of how you might use them.
The
humans
table contains a column holding the date in which a person's membership ex-
pires,
membership_expiration
. Suppose that we want to display the number of days
until their membership expires on the member's profile page, to remind them. For that re-
quirement, we can use the
DATEDIFF()
function in an SQL statement similar to the fol-
lowing:
SELECT CURDATE() AS 'Today',
DATE_FORMAT(membership_expiration, '%M %e, %Y')
AS 'Date Membership Expires',
DATEDIFF(membership_expiration, CURDATE())
AS 'Days Until Expiration'
FROM humans
WHERE human_id = 4;
+------------+-------------------------+-----------------------+
| Today | Date Membership Expires | Days Until Expiration |
+------------+-------------------------+-----------------------+
| 2014-02-13 | September 22, 2013 | -144 |
+------------+-------------------------+-----------------------+
Notice that the result here from
DATEDIFF()
is a negative amount. That's because the
date contained in
membership_expiration
is a date before the current date, the date
when
CURDATE()
was executed. If you swapped the two values given for
DATEDIFF()
,
the results would be positive. If you want to know only the number of days apart the two
dates are, and don't care which comes first, you can use
ABS()
with
DATEDIFF()
to get
the absolute value no matter how you order them. Incidentally, although you may give val-
ues in date and time formats, only the date portions are used for determining the difference.
Similar to
DATEDIFF()
, you can get the difference between time valuesusing the
TIMEDIFF()
function. Before looking at an example of it, let's create a new table that
uses dates and times. Suppose we've decided to organize and sponsor birding events, out-
ings in which bird-watchers will go together to look for interesting birds. To store that in-
formation, we'll create a table called
birding_events
in the
birdwatchers
data-
base:
