Using Date and Time Functions in MySQL

MySQL's built-in date-related functions can be used in SELECT statements, with or without specifying a table, to retrieve a result of the function. Or you can use the functions with any type of date field: date, datetime, timestamp, and year. Depending on the type of field in use, the results of the date-related functions are more or less useful.

Working with Days

The DAYOFWEEK() and WEEKDAY() functions do similar things with slightly different results. Both functions are used to find the weekday index of a date, but the difference lies in the starting day and position.

If you use DAYOFWEEK(), the first day of the week is Sunday, at position 1, and the last day of the week is Saturday, at position 7. For example

mysql> select dayofweek('2004-08-23');
+-------------------------+
| dayofweek('2004-08-23') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

The result shows that August 23, 2004 was weekday index 2, or Monday. Using the same date with WEEKDAY() gives you a different result with the same meaning:

mysql> select weekday('2004-08-23');
+-----------------------+
| WEEKDAY('2004-08-23') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

The result shows that August 23, 2004 was weekday index 0. Because WEEKDAY() uses Monday as the first day of the week at position 0 and Sunday as the last day at position 6, 0 is accurate: Monday.

The DAYOFMONTH() and DAYOFYEAR() functions are more straightforward, with only one result and a range that starts at 1 and ends at 31 for DAYOFMONTH() and 366 for DAYOFYEAR(). Some examples follow:

mysql> select dayofmonth('2004-08-23');
+--------------------------+
| DAYOFMONTH('2004-08-23') |
+--------------------------+
|                       23 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select dayofyear('2004-08-23');
+-------------------------+
| DAYOFYEAR('2004-08-23') |
+-------------------------+
|                     236 |
+-------------------------+
1 row in set (0.00 sec)

It might seem odd to have a function that returns the day of the month on a particular date because the day is right there in the string. But think about using these types of functions in WHERE clauses to perform comparisons on records. If you have a table that holds online orders with a field containing the date the order was placed, you can quickly get a count of the orders placed on any given day of the week, or see how many orders were placed during the first half of the month versus the second half.

The following two queries show how many orders were placed during the first three days of the week (throughout all months) and then the remaining days of the week:

mysql> select count(id) from orders where dayofweek(date_ordered) < 4;
+-----------+
| COUNT(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from orders where dayofweek(date_ordered) > 3;
+-----------+
| COUNT(id) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

Using DAYOFMONTH(), the following examples show the number of orders placed during the first half of any month versus the second half:

mysql> select count(id) from orders where dayofmonth(date_ordered) < 16;
+-----------+
| COUNT(id) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from orders where dayofmonth(date_ordered) > 15;
+-----------+
| COUNT(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

You can use the DAYNAME() function to add more life to your results because it returns the name of the weekday for any given date:

mysql> select dayname(date_ordered) from orders;
+-----------------------+
| DAYNAME(date_ordered) |
+-----------------------+
| Thursday              |
| Monday                |
| Thursday              |
| Thursday              |
| Wednesday             |
| Thursday              |
| Sunday                |
| Sunday                |
+-----------------------+
8 rows in set (0.00 sec)

Functions aren't limited to WHERE clausesyou can use them in ORDER BY clauses as well:

mysql> select dayname(date_ordered) from orders
    -> order by dayofweek(date_ordered);
+-----------------------+
| DAYNAME(date_ordered) |
+-----------------------+
| Sunday                |
| Sunday                |
| Monday                |
| Wednesday             |
| Thursday              |
| Thursday              |
| Thursday              |
| Thursday              |
+-----------------------+
8 rows in set (0.00 sec)

Working with Months and Years

Days of the week aren't the only parts of the calendar, and MySQL has functions specifically for months and years as well. Just like the DAYOFWEEK() and DAYNAME() functions, MONTH() and MONTHNAME() return the number of the month in a year and the name of the month for a given date. For example

mysql> select month('2004-08-23'), monthname('2004-08-23');
+---------------------+-------------------------+
| month('2004-08-23') | monthname('2004-08-23') |
+---------------------+-------------------------+
|                   8 | August                  |
+---------------------+-------------------------+
1 row in set (0.00 sec)

Using MONTHNAME() on the orders table shows the proper results but a lot of repeated data:

mysql> select monthname(date_ordered) from orders;
+-------------------------+
| MONTHNAME(date_ordered) |
+-------------------------+
| November                |
| November                |
| November                |
| November                |
| November                |
| November                |
| November                |
| October                 |
+-------------------------+
8 rows in set (0.00 sec)

You can use DISTINCT to get nonrepetitive results:

mysql> select distinct monthname(date_ordered) from orders;
+-------------------------+
| MONTHNAME(date_ordered) |
+-------------------------+
| November                |
| October                 |
+-------------------------+
2 rows in set (0.00 sec)

For work with years, the YEAR() function will return the year of a given date:

mysql> select distinct year(date_ordered) from orders;
+--------------------+
| YEAR(date_ordered) |
+--------------------+
|               2003 |
+--------------------+
1 row in set (0.00 sec)

Working with Weeks

Weeks can be tricky thingsthere can be 53 weeks in a year if Sunday is the first day of the week and December hasn't ended. For example, December 30th of 2001 was a Sunday:

mysql> select dayname('2001-12-30');
+-----------------------+
| DAYNAME('2001-12-30') |
+-----------------------+
| Sunday                |
+-----------------------+
1 row in set (0.00 sec)

That fact made December 30 of 2001 part of the 53rd week of the year:

mysql> select week('2001-12-30');
+--------------------+
| WEEK('2001-12-30') |
+--------------------+
|                 53 |
+--------------------+
1 row in set (0.00 sec)

The 53rd week contains December 30 and 31, and is only two days long; the first week of 2002 begins with January 1.

If you want your weeks to start on Mondays but still want to find the week of the year, the optional second argument enables you to change the start day. A 1 indicates a week that starts on Monday. In the following examples, a Monday start day makes December 30 part of the 52nd week of 2001, but December 31 is still part of the 53rd week of 2001.

mysql> select week('2001-12-30',1);
+----------------------+
| WEEK('2001-12-30',1) |
+----------------------+
|                   52 |
+----------------------+
1 row in set (0.00 sec)

mysql> select week('2001-12-31',1);
+----------------------+
| WEEK('2001-12-31',1) |
+----------------------+
|                   53 |
+----------------------+
1 row in set (0.00 sec)

Working with Hours, Minutes, and Seconds

If you're using a date that includes the exact time, such as datetime or timestamp, or even just a time field, there are functions to find the hours, minutes, and seconds from that string. Not surprisingly, these functions are called HOUR(), MINUTE(), and SECOND(). HOUR() returns the hour in a given time, which is between 0 and 23. The range for MINUTE() and SECOND() is 0 to 59.

Here are some examples:

mysql> select hour('2004-08-25 07:27:49') as hour,minute('2004-08-25 07:27:49')
    -> as minute,second('2004-08-25 07:27:49') as second;
+------+--------+--------+
| hour | minute | second |
+------+--------+--------+
|    7 |     27 |     49 |
+------+--------+--------+
1 row in set (0.00 sec)

That's a lot of queries to get at one time from a datetime fieldyou can put the hour and minute together and even use CONCAT_WS() to put the : between the results and get a representation of the time:

mysql> select concat_ws(':',hour('2004-08-25 07:27:49'),
    -> minute('2004-08-25 07:27:49')) as sample_time;
+-------------+
| sample_time |
+-------------+
| 7:27        |
+-------------+
1 row in set (0.00 sec)

If you use field names instead of strings, remember not to use quotation marks. Here's an example that uses the dateadded field from the sometable table:

mysql> select concat_ws(':',hour(dateadded), minute(dateadded))
    -> as sample_time from sometable;
+-------------+
| sample_time |
+-------------+
| 13:11       |
| 13:11       |
| 13:11       |
| 13:11       |
| 14:16       |
| 10:12       |
| 10:12       |
| 10:12       |
| 10:12       |
+-------------+
9 rows in set (0.00 sec)

This is cheating because it's not the actual timeit's just two numbers stuck together to look like a time. If you used the concatenation trick on a time such as 02:02, the result would be 2:2, as shown here:

mysql> select concat_ws(':',hour('02:02'), minute('02:02')) as sample_time;
+-------------+
| sample_time |
+-------------+
| 2:2         |
+-------------+
1 row in set (0.00 sec)

This result is obviously not the intended result. In the next section, you learn how to use the DATE_FORMAT() function to properly format dates and times.

Formatting Dates and Times with MySQL

The DATE_FORMAT() function formats a date, datetime, or timestamp field into a string by using options that tell it exactly how to display the results. The syntax of DATE_FORMAT() is

DATE_FORMAT(date,format)

There are many formatting options, as shown in Table 16.2.

Table 16.2. DATE_FORMAT() Format String Options

Option	Result
%M	Month name (January through December)
%b	Abbreviated month name (Jan through Dec)
%m	Month, padded digits (01 through 12)
%c	Month (1 through 12)
%W	Weekday name (Sunday through Saturday)
%a	Abbreviated weekday name (Sun through Sat)
%D	Day of the month using the English suffix, such as first, second, third, and so on
%d	Day of the month, padded digits (00 through 31)
%e	Day of the month (0 through 31)
%j	Day of the year, padded digits (001 through 366)
%Y	Year, four digits
%y	Year, two digits
%X	Four-digit year for the week where Sunday is the first day; used with %V
%x	Four-digit year for the week where Monday is the first day; used with %v
%w	Day of the week (0=Sunday...6=Saturday)
%U	Week (0 through 53) where Sunday is the first day of the week
%u	Week (0 through 53) where Monday is the first day of the week
%V	Week (1 through 53) where Sunday is the first day of the week; used with %X
%v	Week (1 through 53) where Monday is the first day of the week; used with %x
%H	Hour, padded digits (00 through 23)
%k	Hour (0 through 23)
%h	Hour, padded digits (01 through 12)
%l	Hour (1 through 12)
%i	Minutes, padded digits (00 through 59)
%S	Seconds, padded digits (00 through 59)
%s	Seconds, padded digits (00 through 59)
%r	Time, 12-hour clock (hh:mm:ss [AP]M)
%T	Time, 24-hour clock (hh:mm:ss)
%p	AM or PM

To display the 02:02 result that we rigged in the previous section, you'd use the %h and %i options to return the hour and minute from the date with a : between the two options. For example

mysql> select date_format('2004-08-25 02:02:00', '%h:%i') as sample_time;
+-------------+
| sample_time |
+-------------+
| 02:02       |
+-------------+
1 row in set (0.00 sec)

The following are just a few more examples of the DATE_FORMAT() function in use, but this function is best understood by practicing it yourself.

mysql> select date_format('2004-08-23', '%W, %M %D, %Y') as sample_time;
+------------------------------+
| sample_time                  |
+------------------------------+
| Monday, August 23rd, 2004    |
+------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%W the %D of %M, %Y around %l o\'clock %p')
    -> as sample_time;
+--------------------------------------------------------+
| sample_time                                            |
+--------------------------------------------------------+
| Monday the 23rd of August, 2004 around 8 o'clock AM    |
+--------------------------------------------------------+
1 row in set (0.00 sec)

If you're working specifically with time fields, the TIME_FORMAT() function works just like the DATE_FORMAT() function. Only the format options for hours, minutes, and seconds are allowed:

mysql> select time_format('02:02:00', '%h:%i') as sample_time;
+-------------+
| sample_time |
+-------------+
| 02:02       |
+-------------+
1 row in set (0.00 sec)

Performing Date Arithmetic with MySQL

MySQL has several functions to help perform date arithmetic, and this is one of the areas where it might be quicker to allow MySQL to do the math than your PHP script. The DATE_ADD() and DATE_SUB() functions return a result given a starting date and an interval. The syntax for both functions is

DATE_ADD(date,INTERVAL value type)

DATE_SUB(date,INTERVAL value type)

Table 16.3 shows the possible types and their expected value format.

Table 16.3. Values and Types in Date Arithmetic

Value	Type
Number of seconds	SECOND
Number of minutes	MINUTE
Number of hours	HOUR
Number of days	DAY
Number of months	MONTH
Number of years	YEAR
"minutes:seconds"	MINUTE_SECOND
"hours:minutes"	HOUR_MINUTE
"days hours"	DAY_HOUR
"years-months"	YEAR_MONTH
"hours:minutes:seconds"	HOUR_SECOND
"days hours:minutes"	DAY_MINUTE
"days hours:minutes:seconds"	DAY_SECOND

For example, to find the date of the current day plus 21 days, use the following:

mysql> select date_add(now(), interval 21 day);
+----------------------------------+
| date_add(now(), interval 21 day) |
+----------------------------------+
| 2004-09-17 13:07:34              |
+----------------------------------+
1 row in set (0.00 sec)

To subtract 21 days, use

mysql> select date_sub(now(), interval 21 day);
+----------------------------------+
| date_sub(now(), interval 21 day) |
+----------------------------------+
| 2004-08-06 13:07:49              |
+----------------------------------+
1 row in set (0.00 sec)

Use the expression as it's shown in Table 16.3, despite what might be a natural tendency to use DAYS instead of DAY. Using DAYS results in an error:

mysql> select date_add(now(), interval 21 days);
ERROR 1064: You have an error in your SQL syntax near 'days)' at line 1

If you're using DATE_ADD() or DATE_SUB() with a date value instead of a datetime value, the result will be shown as a date value unless you use expressions related to hours, minutes, and seconds. In that case, your result will be a datetime result.

For example, the result of the first query remains a date field, whereas the second becomes a datetime:

mysql> select date_add("2001-12-31", interval 1 day);
+----------------------------------------+
| DATE_ADD("2001-12-31", INTERVAL 1 DAY) |
+----------------------------------------+
| 2002-01-01                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add("2001-12-31", interval 12 hour);
+------------------------------------------+
| DATE_ADD("2001-12-31", INTERVAL 12 HOUR) |
+------------------------------------------+
| 2001-12-31 12:00:00                      |
+------------------------------------------+
1 row in set (0.00 sec)

Beginning with MySQL version 3.23, you can also perform date arithmetic using the + and - operators instead of DATE_ADD() and DATE_SUB() functions:

mysql> select "2001-12-31" + interval 1 day;
+-------------------------------+
| "2001-12-31" + INTERVAL 1 DAY |
+-------------------------------+
| 2002-01-01                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select "2001-12-31" - interval 14 hour;
+---------------------------------+
| "2001-12-31" - INTERVAL 14 HOUR |
+---------------------------------+
| 2001-12-30 10:00:00             |
+---------------------------------+
1 row in set (0.00 sec)

Special Functions and Conversion Features

The MySQL NOW() function returns a current datetime result, and is useful for timestamping login or access times, as well as numerous other tasks. MySQL has a few other functions that perform similarly.

The CURDATE() and CURRENT_DATE() functions are synonymous, and each returns the current date in YYYY-MM-DD format:

mysql> select curdate(), current_date();
+------------+----------------+
| curdate()  | current_date() |
+------------+----------------+
| 2004-08-25 | 2004-08-25     |
+------------+----------------+
1 row in set (0.01 sec)

Similarly, the CURTIME() and CURRENT_TIME() functions return the current time in HH:MM:SS format:

mysql> select curtime(), current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 09:14:26  | 09:14:26       |
+-----------+----------------+
1 row in set (0.00 sec)

The NOW(), SYSDATE(), and CURRENT_TIMESTAMP() functions return values in full datetime format (YYYY-MM-DD HH:MM:SS):

mysql> select now(), sysdate(), current_timestamp();
+---------------------+---------------------+---------------------+
| now()               | sysdate()           | current_timestamp() |
+---------------------+---------------------+---------------------+
| 2004-08-25 09:14:50 | 2004-08-25 09:14:50 | 2004-08-25 09:14:50 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

The UNIX_TIMESTAMP() function returns the current date inor converts a given date toUnix timestamp format. Unix timestamp format is in seconds since the epoch, or seconds since midnight, January 1, 1970. For example

mysql> select unix_timestamp();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1093637311|
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1973-12-30');
+------------------------------+
| UNIX_TIMESTAMP('1973-12-30') |
+------------------------------+
|                    126086400 |
+------------------------------+
1 row in set (0.00 sec)

The FROM_UNIXTIME() function performs a conversion of a Unix timestamp to a full datetime format when used without any options:

mysql> select from_unixtime(' 1093637311');
+-----------------------------+
| from_unixtime('1061767446') |
+-----------------------------+
| 2004-08-27 13:08:31         |
+-----------------------------+
1 row in set (0.00 sec)

You can use the format options from the DATE_FORMAT() functions to display a timestamp in a more appealing manner:

mysql> select from_unixtime(unix_timestamp(), '%D %M %Y at %h:%i:%s');
+---------------------------------------------------------+
| from_unixtime(unix_timestamp(), '%D %M %Y at %h:%i:%s') |
+---------------------------------------------------------+
| 27th August 2004 at 01:09:20                            |
+---------------------------------------------------------+
1 row in set (0.00 sec)

If you're working with a number of seconds and want to convert the seconds to a time-formatted result, you can use SEC_TO_TIME() and TIME_TO_SEC() to convert values back and forth.

For example, 1440 seconds is equal to 24 minutes and vice versa:

mysql> select sec_to_time('1440'), time_to_sec('00:24:00');
+---------------------+-------------------------+
| SEC_TO_TIME('1440') | TIME_TO_SEC('00:24:00') |
+---------------------+-------------------------+
| 00:24:00            |                    1440 |
+---------------------+-------------------------+
1 row in set (0.01 sec)