MySQL Cookbook, 2nd Edition

Extracting Parts of Dates or Times

Problem

You want to obtain just a part of a date or a time.

Solution

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.

Discussion

The following discussion shows different ways to extract parts of temporal values.

Decomposing dates or times using component-extraction functions

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 ...