Database Reference
In-Depth Information
| d | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+------------+------------+--------------+------------+
| 1864-02-28 | Sunday | 1 | 6 |
| 1900-01-15 | Monday | 2 | 0 |
| 1999-12-31 | Friday | 6 | 4 |
| 2000-06-04 | Sunday | 1 | 6 |
| 2017-03-16 | Thursday | 5 | 3 |
+------------+------------+--------------+------------+
EXTRACT()
is another function for obtaining individual parts of temporal values:
mysql>
SELECT dt, EXTRACT(DAY FROM dt), EXTRACT(HOUR FROM dt)
->
FROM datetime_val;
+---------------------+----------------------+-----------------------+
| dt | EXTRACT(DAY FROM dt) | EXTRACT(HOUR FROM dt) |
+---------------------+----------------------+-----------------------+
| 1970-01-01 00:00:00 | 1 | 0 |
| 1999-12-31 09:00:00 | 31 | 9 |
| 2000-06-04 15:45:30 | 4 | 15 |
| 2017-03-16 12:30:15 | 16 | 12 |
+---------------------+----------------------+-----------------------+
The keyword indicating what to extract from the value should be a unit specifier such
as
YEAR
,
MONTH
,
DAY
,
HOUR
,
MINUTE
, or
SECOND
. Unit specifiers are singular, not plural.
(Check the
MySQL Reference Manual
for the full list.)
Obtaining the Current Year, Month, Day, Hour, Minute, or Second
To obtain the current year, month, day, or day of week, apply the extraction functions
shown in this recipe to
CURDATE()
or
NOW()
:
mysql>
SELECT CURDATE(), YEAR(CURDATE()) AS year,
->
MONTH(CURDATE()) AS month, MONTHNAME(CURDATE()) AS monthname,
->
DAYOFMONTH(CURDATE()) AS day, DAYNAME(CURDATE()) AS dayname;
+------------+------+-------+-----------+------+----------+
| CURDATE() | year | month | monthname | day | dayname |
+------------+------+-------+-----------+------+----------+
| 2014-02-20 | 2014 | 2 | February | 20 | Thursday |
+------------+------+-------+-----------+------+----------+
Similarly, to obtain the current hour, minute, or second, pass
CURTIME()
or
NOW()
to a
time-component function:
mysql>
SELECT NOW(), HOUR(NOW()) AS hour,
->
MINUTE(NOW()) AS minute, SECOND(NOW()) AS second;
+---------------------+------+--------+--------+
| NOW() | hour | minute | second |
+---------------------+------+--------+--------+
| 2014-02-20 18:07:03 | 18 | 7 | 3 |
+---------------------+------+--------+--------+
