Working with Dates and Times - MySQL - page 196

Here's an example:
mysql> SELECT dt, YEAR(dt), DAYOFMONTH(dt), HOUR(dt), SECOND(dt)
-> FROM datetime_val;
+---------------------+----------+----------------+----------+------------+
| dt | YEAR(dt) | DAYOFMONTH(dt) | HOUR(dt) | SECOND(dt) |
+---------------------+----------+----------------+----------+------------+
| 1970-01-01 00:00:00 | 1970 | 1 | 0 | 0 |
| 1999-12-31 09:00:00 | 1999 | 31 | 9 | 0 |
| 2000-06-04 15:45:30 | 2000 | 4 | 15 | 30 |
| 2017-03-16 12:30:15 | 2017 | 16 | 12 | 15 |
+---------------------+----------+----------------+----------+------------+
Functions such as YEAR() or DAYOFMONTH() extract values that have an obvious corre‐
spondence to a substring of the temporal value to which you apply them. Other
component-extraction functions provide access to values that have no such corre‐
spondence. One is the day-of-year value:
mysql> SELECT d, DAYOFYEAR(d) FROM date_val;
+------------+--------------+
| d | DAYOFYEAR(d) |
+------------+--------------+
| 1864-02-28 | 59 |
| 1900-01-15 | 15 |
| 1999-12-31 | 365 |
| 2000-06-04 | 156 |
| 2017-03-16 | 75 |
+------------+--------------+
Another is the day of the week, which is available by name or number:
• DAYNAME() returns the complete day name. There is no function for returning the
three-character name abbreviation, but you can get it easily by passing the full name
to LEFT() :
mysql> SELECT d, DAYNAME(d), LEFT(DAYNAME(d),3) FROM date_val;
+------------+------------+--------------------+
| d | DAYNAME(d) | LEFT(DAYNAME(d),3) |
+------------+------------+--------------------+
| 1864-02-28 | Sunday | Sun |
| 1900-01-15 | Monday | Mon |
| 1999-12-31 | Friday | Fri |
| 2000-06-04 | Sunday | Sun |
| 2017-03-16 | Thursday | Thu |
+------------+------------+--------------------+
• To get the day of the week as a number, use DAYOFWEEK() or WEEKDAY() , but pay
attention to the range of values each function returns. DAYOFWEEK() returns values
from 1 to 7, corresponding to Sunday through Saturday. WEEKDAY() returns values
from 0 to 6, corresponding to Monday through Sunday:
mysql> SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) FROM date_val;
+------------+------------+--------------+------------+