5.7 Decomposing Dates or Times Using String Functions
5.7.1 Problem
You want to obtain just a part
of a date or a time.
5.7.2 Solution
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.
5.7.3 Discussion
Recipe 5.5 and Recipe 5.6
discuss how to extract components of temporal values using
DATE_FORMAT( ) or functions such as YEAR(
) and MONTH( ). If you pass a date or
time value to a string function, MySQL treats it as a string, which
means you can extract substrings. Thus, yet another way to extract
pieces of temporal values is to use string functions such as
LEFT( ) or MID( ).
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 pull out the entire date or time part from
DATETIME 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 |
+---------------------+------------+----------+
The same technique also works for TIMESTAMP
values. However, because these contain no delimiter characters, the
indexes for LEFT( ) and RIGHT(
) are a little different, as are the formats of the output
values:
mysql> SELECT ts,
-> LEFT(ts,8) AS date,
-> RIGHT(ts,6) AS time
-> FROM timestamp_val;
+----------------+----------+--------+
| ts | date | time |
+----------------+----------+--------+
| 19700101000000 | 19700101 | 000000 |
| 19870305123015 | 19870305 | 123015 |
| 19991231090000 | 19991231 | 090000 |
| 20000604154530 | 20000604 | 154530 |
+----------------+----------+--------+
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. MySQL might interpret the value
1987-1-1 as 1987-01-01 if you
insert it into a DATE column, but using
RIGHT('1987-1-1',2) to extract the day part will
not work. If the values have variable-length substrings, you may be
able to use SUBSTRING_INDEX( ) instead.
Alternatively, if your values are close to ISO format, you can
standardize them using the techniques described in Recipe 5.19.
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.
|
