Decomposing Dates or Times Using String Functions

2017-11-03 09:05:05

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.

Категории