You have several options:
Invoke a function specifically intended for extracting part
of a temporal value, such as
MONTH()
or
MINUTE()
. This is usually
the fastest method for component extraction if you need only a
single component of a value.
Use a formatting function such as
DATE_FORMAT()
or TIME_FORMAT()
with a format string that includes a specifier for
the part of the value you want to obtain.
Treat a temporal value as a string and use a function such
as
LEFT()
or
MID()
to extract substrings corresponding to the desired
part of the value.
The following discussion shows different ways to extract parts of temporal values.
MySQL includes many functions for extracting date or time
parts from temporal values. For example, DATE()
or TIME()
extracts the date or time part of temporal values:
mysql>SELECT dt, DATE(dt), TIME(dt) FROM datetime_val;
+---------------------+------------+----------+
| dt | DATE(dt) | TIME(dt) |
+---------------------+------------+----------+
| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |
| 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 |
| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |
| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |
+---------------------+------------+----------+
Some of the other component-extraction functions are shown in
the following list; consult the MySQL Reference
Manual for a complete list. The date-related functions
work with DATE
, DATETIME
, or TIMESTAMP
values. The time-related
functions work with TIME
,
DATETIME
, or TIMESTAMP
values.
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 | | 1987-03-05 12:30:15 | 1987 | 5 | 12 | 15 | | 1999-12-31 09:00:00 | 1999 | 31 | 9 | 0 | | 2000-06-04 15:45:30 | 2000 | 4 | 15 | 30 | +---------------------+----------+----------------+----------+------------+
Functions such as YEAR()
or DAYOFMONTH()
extract values that
have an obvious correspondence to a substring of the temporal value
to which you apply them. Other date component-extraction functions
provide access to values that have no such correspondence. 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 |
| 1987-03-05 | 64 |
| 1999-12-31 | 365 |
| 2000-06-04 | 156 |
+------------+--------------+
Another is the day of the week, which can be obtained either by name or by 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 |
| 1987-03-05 | Thursday | Thu |
| 1999-12-31 | Friday | Fri |
| 2000-06-04 | Sunday | Sun |
+------------+------------+--------------------+
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;
+------------+------------+--------------+------------+
| d | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+------------+------------+--------------+------------+
| 1864-02-28 | Sunday | 1 | 6 |
| 1900-01-15 | Monday | 2 | 0 |
| 1987-03-05 | Thursday | 5 | 3 |
| 1999-12-31 | Friday | 6 | 4 |
| 2000-06-04 | Sunday | 1 | 6 |
+------------+------------+--------------+------------+
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 | | 1987-03-05 12:30:15 | 5 | 12 | | 1999-12-31 09:00:00 | 31 | 9 | | 2000-06-04 15:45:30 | 4 | 15 | +---------------------+----------------------+-----------------------+
The keyword indicating what to extract from the value should
be a unit specifier such as YEAR
,
MONTH
, DAY
, HOUR
, MINUTE
, or SECOND
. (Check the MySQL
Reference Manual for the full list.) Note that each unit
specifier is given in singular form, not plural.
The DATE_FORMAT()
and TIME_FORMAT()
functions reformat date and time values. By specifying
appropriate format strings, you can extract individual parts of
temporal values:
mysql>SELECT dt,
->DATE_FORMAT(dt,'%Y') AS year,
->DATE_FORMAT(dt,'%d') AS day,
->TIME_FORMAT(dt,'%H') AS hour,
->TIME_FORMAT(dt,'%s') AS second
->FROM datetime_val;
+---------------------+------+------+------+--------+ | dt | year | day | hour | second | +---------------------+------+------+------+--------+ | 1970-01-01 00:00:00 | 1970 | 01 | 00 | 00 | | 1987-03-05 12:30:15 | 1987 | 05 | 12 | 15 | | 1999-12-31 09:00:00 | 1999 | 31 | 09 | 00 | | 2000-06-04 15:45:30 | 2000 | 04 | 15 | 30 | +---------------------+------+------+------+--------+
Formatting functions enable you to extract more than one part
of a value. For example, to extract the entire date or time from
DATETIME
values, do this:
mysql>SELECT dt,
->DATE_FORMAT(dt,'%Y-%m-%d') AS 'date part',
->TIME_FORMAT(dt,'%T') AS 'time part'
->FROM datetime_val;
+---------------------+------------+-----------+ | dt | date part | time part | +---------------------+------------+-----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 | +---------------------+------------+-----------+
One advantage of using formatting functions is that you can
display the extracted values in a different form from that in which
they’re present in the original values. If you want to present a
date differently from CCYY-MM-DD
format
or present a time without the seconds part, that’s easy to
do:
mysql>SELECT ts,
->DATE_FORMAT(ts,'%M %e, %Y') AS 'descriptive date',
->TIME_FORMAT(ts,'%H:%i') AS 'hours/minutes'
->FROM timestamp_val;
+---------------------+-------------------+---------------+ | ts | descriptive date | hours/minutes | +---------------------+-------------------+---------------+ | 1970-01-01 00:00:00 | January 1, 1970 | 00:00 | | 1987-03-05 12:30:15 | March 5, 1987 | 12:30 | | 1999-12-31 09:00:00 | December 31, 1999 | 09:00 | | 2000-06-04 15:45:30 | June 4, 2000 | 15:45 | +---------------------+-------------------+---------------+
The discussion in this section thus far has shown how to
extract components of temporal values using functions such as
YEAR()
, MONTH()
, and DATE_FORMAT()
. If you pass a date or
time value to a string function, MySQL treats it as a string, so
string functions provide another way to decompose temporal values.
This means that you can extract pieces of temporal values by using
functions such as LEFT()
or
MID()
to pull out substrings:
mysql>SELECT dt,
->LEFT(dt,4) AS year,
->MID(dt,9,2) AS day,
->RIGHT(dt,2) AS second
->FROM datetime_val;
+---------------------+------+------+--------+ | dt | year | day | second | +---------------------+------+------+--------+ | 1970-01-01 00:00:00 | 1970 | 01 | 00 | | 1987-03-05 12:30:15 | 1987 | 05 | 15 | | 1999-12-31 09:00:00 | 1999 | 31 | 00 | | 2000-06-04 15:45:30 | 2000 | 04 | 30 | +---------------------+------+------+--------+
You can obtain the entire date or time part from DATETIME
or TIMESTAMP
values using string-extraction
functions such as LEFT()
or
RIGHT()
:
mysql>SELECT dt,
->LEFT(dt,10) AS date,
->RIGHT(dt,8) AS time
->FROM datetime_val;
+---------------------+------------+----------+ | dt | date | time | +---------------------+------------+----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 | +---------------------+------------+----------+ mysql>SELECT ts,
->LEFT(ts,10) AS date,
->RIGHT(ts,8) AS time
->FROM timestamp_val;
+---------------------+------------+----------+ | ts | date | time | +---------------------+------------+----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 | | 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 | | 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 | +---------------------+------------+----------+
Decomposition of temporal values with string functions is subject to a couple of constraints that component extraction and reformatting functions are not bound by:
To use a substring function such as LEFT()
, MID()
, or RIGHT()
, you must have
fixed-length strings. Although MySQL interprets the value
1987-1-1
as 1987-01-01
if you insert it into a
DATE
column, using RIGHT('1987-1-1',2)
to extract the day
part will not work. For values that have variable-length
substrings, you may be able to use SUBSTRING_INDEX()
instead.
Alternatively, if your values are close to ISO format, perhaps
you can standardize them to ISO format using the techniques
described in Canonizing Not-Quite-ISO Date Strings. This
converts them to have fixed-length substrings before you attempt
to extract subparts.
String functions cannot be used to obtain values that don’t correspond to substrings of a date value, such as the day of the week or the day of the year.
3.15.214.155