|
Last time we looked at a few of the more commonly used date
and time functions. This time we're going to look at some of
the less well-known functions. First, we will need to
understand the various date and time types MySQL accepts for
passing to many of these functions.
| SECOND |
ss |
| MINUTE |
mm |
| HOUR |
hh |
| DAY |
DD |
| MONTH |
MM |
| YEAR |
YY |
| MINUTE_SECOND |
mm:ss |
| HOUR_MINUTE |
hh:mm |
| DAY_HOUR |
DD:hh |
| YEAR_MONTH |
YYYY-MM |
| HOUR_SECOND |
hh:ss |
| DAY_MINUTE |
DD hh:mm |
| DAY_SECOND |
DD hh:mm:ss |
As you can see, there are quite a few. Notice that the ones
spanning a number of time elements (such as DAY_SECOND)
include all the elements between DAY and SECOND (in this case
HOUR and MINUTE). In the last article, we looked at a specific
date calculation to determine age. However, there's a lot more
you can do in MySQL before you need to call upon a programming
language for assistance.
Adding and subtracting dates and times with DATE_ADD() and
DATE_SUB()
The DATE_ADD() function - ADDDATE() is a
synonmym - is used to add a particular date or time interval
to a give date or time.
DATE_ADD(datetime, INTERVAL expression datetimetype)
For example, to find a date 14 days after the 13th July, 2003,
you can use:
mysql> SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
+-----------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL 14 DAY) |
+-----------------------------------------+
| 2003-07-27 |
+-----------------------------------------+
You can also use a negative expression to
subtract datetimes. To find the datetime 22 hours and 14
minutes before the the 13th July, 2003, 1 minute and 1 second
past 1, you can use:
mysql> SELECT DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE);
+----------------------------------------------------------------+
| DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE) |
+----------------------------------------------------------------+
| 2003-07-13 00:39:01 |
+----------------------------------------------------------------+
Note that when using a datetime type that
requires more than a simple numeric, you need to use quotes to
contain the entire expression.
You can also mix date and time types, and
MySQL will do its best to make do. For example:
mysql> SELECT DATE_ADD('2003-07-13', INTERVAL -1 MINUTE);
+--------------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL -1 MINUTE) |
+--------------------------------------------+
| 2003-07-12 23:59:00 |
+--------------------------------------------+
or
mysql> SELECT DATE_ADD('2003-07-13', INTERVAL '-22:14' HOUR_MINUTE);
+-------------------------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL '-22:14' HOUR_MINUTE) |
+-------------------------------------------------------+
| 2003-07-12 01:46:00 |
+-------------------------------------------------------+
Be careful about where you put the minus sign.
When you use quotes, and place the minus sign outside of the
quotes, you may not get what you expect. Here's the same
example with the minus sign moved:
mysql> SELECT DATE_ADD('2003-07-14', INTERVAL -'22:14' HOUR_MINUTE);
+-------------------------------------------------------+
| DATE_ADD('2003-07-14', INTERVAL -'22:14' HOUR_MINUTE) |
+-------------------------------------------------------+
| 2003-07-13 23:38:00 |
+-------------------------------------------------------+
Probably not what you were looking for!
There's an alternative to using a negative
number with the DATE_ADD() function - you could simply use
DATE_SUB(), or its synonym SUBDATE(). There is also an
alternative if you're only worried about the YEAR and MONTH
components of the date. You can use the PERIOD_ADD() and
PERIOD_DIFF() functions. PERIOD_ADD takes a period (specified
as YYYYMM or YYMM), and adds a number of months
PERIOD_ADD(period,months)
For example:
mysql> SELECT PERIOD_ADD(200312,43);
+-----------------------+
| PERIOD_ADD(200312,43) |
+-----------------------+
| 200707 |
+-----------------------+
As always, you can use a negative to subtract,
as the next example shows (note the two-digit year).
mysql> SELECT PERIOD_ADD(0312,-32);
+----------------------+
| PERIOD_ADD(0312,-32) |
+----------------------+
| 200104 |
+----------------------+
Conversely, the PERIOD_DIFF function returns
the difference, in months, between two periods. Again, the
periods can be specified as both YYYYMM and YYMM.
PERIOD_DIFF(period,period)
For example:
mysql> SELECT PERIOD_DIFF(200104,0312);
+--------------------------+
| PERIOD_DIFF(200104,0312) |
+--------------------------+
| -32 |
+--------------------------+
Logo
|
