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:
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:
PERIOD_DIFF(period,period)
For example:
mysql> SELECT PERIOD_DIFF(200104,0312);
+--------------------------+
| PERIOD_DIFF(200104,0312) |
+--------------------------+
| -32 |
+--------------------------+
Date formats
The DATE_FORMAT function is a powerful function that allows you to return a specified date in a number of different ways. For example, those who use American time (MM-DD-YYYY, as you can see at the beginning of this article) instead of the standard International time, this function provides a painless way to convert. Below is a list of format specifiers:
| %a | Abbreviation of the day (from Sun-Sat) |
| %b | Abbreviation of the month (from Jan-Dec) |
| %c | Numeric month (from 1-12) |
| %D | Numeric day of the month with suffix (1st, 2nd, and so on) |
| %d | Numeric day of the month with two digits(from 00-31) |
| %e | Numeric day of the month with one or two digits(from 0-31) |
| %H | Hour (from 00-23) |
| %h | Hour (from 01-12) |
| %i | Minutes (from 00-59) |
| %I | Hour (from 01-12) |
| %j | Day of the year (from 001-366) |
| %k | Hour with one or two digits (from 0-23) |
| %l | Hour with one digit (from 1-12) |
| %M | Month name (from January-December) |
| %m | Numeric month (from 01-12) |
| %p | A.M. or P.M. |
| %r | 12-hour time (hh:mm:ss A.M.or P.M.) |
| %S | Seconds (from 00-59) |
| %s | Seconds (from 00-59) |
| %T | 24 hour time (hh:mm:ss) |
| %U | Week (from 00-53, Sunday being the first day of the week) |
| %u | Week (from 00-53, Monday being the first day of the week) |
| %V | Week (from 01-53, Sunday being the first day of the week) |
| %v | Week (from 01-53, Monday being the first day of the week) |
| %W | Name of the day in the week (from Sunday-Saturday) |
| %w | Day of the week (from 0 – Sunday, to 6 – Saturday) |
| %X | Four-digit numeric year for the week (Sunday being the first day of the week) |
| %x | Four-digit numeric year for the week (Monday being the first day of the week) |
| %Y | Four-digit numeric year |
| %y | Two-digit numeric year |
| %% | Percentage sign (escaped) |
Let’s look at converting a standard date into a format used in the US.
mysql> SELECT DATE_FORMAT('2003-07-14','%b %d,%Y');
+--------------------------------------+
| DATE_FORMAT('2003-07-14','%b %d,%Y') |
+--------------------------------------+
| Jul 14,2003 |
+--------------------------------------+
A subset of this function is the TIME_FORMAT() function, which is identical, but allows you to use those formats to do with time.
The last function we are going to look at specifically will be meaningful to those of you familiar with the concept of Unix time. Unix time is the time in seconds since midnight 1 January 1970, and is used by many applications. The UNIX_TIMESTAMP() function returns the Unix time of the current time When called without a parameter, or converts a specified date if one is supplied.
The last function we are going to look at specifically will be meaningful to those of you familiar with the concept of Unix time. Unix time is the time in seconds since midnight 1 January 1970, and is used by many applications. The UNIX_TIMESTAMP() function returns the Unix time of the current time When called without a parameter, or converts a specified date if one is supplied.
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1050267998 |
+------------------+
mysql> SELECT UNIX_TIMESTAMP('2003-07-14');
+------------------------------+
| UNIX_TIMESTAMP('2003-07-14') |
+------------------------------+
| 1058133600 |
+------------------------------+
Looking at the possibilities available with this function, along with the date and time calculations we’ve looked at already, you can see there’s not always that much need to do this inside the application!
Below is a reference for the MySQL date and time functions. Once you have mastered what we’ve covered so far, none of the other functions will present anything tricky, but you should give the list a read through – you never know when you’ll need them.
Date functions reference
- ADDDATE(datetime, INTERVAL expression datetimetype)
A synonym for DATE_ADD() - CURDATE()
A synonym for CURRENT_DATE() - CURRENT_DATE()
Returns the current system date as YYYY-MM-DD (or YYYYMMDD if the context requires this, such as when you add a numeric to the result)
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2003-04-14 |
+----------------+
Returns the current system time as hh:mm:ss (or hhmmss if the context requires this, such as when you add a numeric to the result)
A synonym for NOW()
mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 12:03:10 |
+----------------+
A synonym for CURRENT_TIME()
Adds the expression to the datetime supplied. The expression can be any valid datetimetype.
mysql> SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
+-----------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL 14 DAY) |
+-----------------------------------------+
| 2003-07-27 |
+-----------------------------------------+
Formats the datetime according to the format string
mysql> SELECT DATE_FORMAT('2003-07-14','%b %d,%Y');
+--------------------------------------+
| DATE_FORMAT('2003-07-14','%b %d,%Y') |
+--------------------------------------+
| Jul 14,2003 |
+--------------------------------------+
The same as DATE_ADD, but subtracting instead of adding
mysql> SELECT DATE_SUB('2003-07-14', INTERVAL 14 DAY);
+-----------------------------------------+
| DATE_SUB('2003-07-14', INTERVAL 14 DAY) |
+-----------------------------------------+
| 2003-06-30 |
+-----------------------------------------+
Returns the full name of the day for the specified date.
mysql> SELECT DAYNAME('2003-07-14');
+-----------------------+
| DAYNAME('2003-07-14') |
+-----------------------+
| Monday |
+-----------------------+
Returns a numeric (from 1-31) for the day of the month
mysql> SELECT DAYOFMONTH('2003-07-14');
+--------------------------+
| DAYOFMONTH('2003-07-14') |
+--------------------------+
| 14 |
+--------------------------+
