Home / Funtions and Operators / Question No: 235

Temporal functions perform operations such as extracting parts of dates and times, reformatting values, or converting values to seconds or days. In many cases, a temporal function that takes a date or time argument also can be given a datetype argument and will ignore the irrelevant part of the datetime value.

There are functions for extracting parts of date or time values:

mysql> SET @d = '2010-04-15', @t = '09:23:57';
mysql> SELECT YEAR(@d), MONTH(@d), DAYOFMONTH(@d);
+----------+-----------+----------------+
| YEAR(@d) | MONTH(@d) | DAYOFMONTH(@d) |
+----------+-----------+----------------+
|     2010 |         4 |             15 |
+----------+-----------+----------------+

mysql> SELECT DAYOFYEAR(@d);
+---------------+
| DAYOFYEAR(@d) |
+---------------+
|           105 |
+---------------+

mysql> SELECT HOUR(@t), MINUTE(@t), SECOND(@t);
+----------+------------+------------+
| HOUR(@t) | MINUTE(@t) | SECOND(@t) |
+----------+------------+------------+
|        9 |         23 |         57 |
+----------+------------+------------+

MAKEDATE() and MAKETIME() compose dates and times from component values. MAKEDATE() produces a date from year and day of year arguments:

mysql> SELECT MAKEDATE(2010,105);
+--------------------+
| MAKEDATE(2010,105) |
+--------------------+
| 2010-04-15         |
+--------------------+

MAKETIME() produces a time from hour, minute, and second arguments.

mysql> SELECT MAKETIME(9,23,57);
+-------------------+
| MAKETIME(9,23,57) |
+-------------------+
| 09:23:57          |
+-------------------+

If you need to determine the current date or time, use CURRENT_DATE or CURRENT_TIME. To get the current date and time as a single value, use CURRENT_TIMESTAMP or NOW():

mysql> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
+--------------+--------------+---------------------+
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP   |
+--------------+--------------+---------------------+
| 2005-05-31   | 21:40:18     | 2005-05-31 21:40:18 |
+--------------+--------------+---------------------+

The three functions in the preceding statement are unlike most functions in that they can be invoked with or without parentheses following the function name.