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.