Database Reference
In-Depth Information
6.9. Synthesizing Dates or Times from Component Values
Problem
You want to combine the parts of a date or time to produce a complete date or time
value. Or you want to replace parts of a date to produce another date.
Solution
You have several options:
• Use
MAKETIME()
to construct a
TIME
value from hour, minute, and second parts.
• Use
DATE_FORMAT()
or
TIME_FORMAT()
to combine parts of the existing value with
parts you want to replace.
• Pull out the parts that you need with component-extraction functions and recom‐
bine the parts with
CONCAT()
.
Discussion
The reverse of splitting a date or time value into components is synthesizing a temporal
value from its constituent parts. Techniques for date and time synthesis include using
composition functions, formatting functions, and string concatenation.
The
MAKETIME()
function takes component hour, minute, and second values as argu‐
ments and combines them to produce a time:
mysql>
SELECT MAKETIME(10,30,58), MAKETIME(-5,0,11);
+--------------------+-------------------+
| MAKETIME(10,30,58) | MAKETIME(-5,0,11) |
+--------------------+-------------------+
| 10:30:58 | -05:00:11 |
+--------------------+-------------------+
Date synthesis often is performed beginning with a given date, then keeping parts that
you want to use and replacing the rest. For example, to produce the first day of the
month in which a date falls, use
DATE_FORMAT()
to extract the year and month parts
from the date, combining them with a day part of
01
:
mysql>
SELECT d, DATE_FORMAT(d,'%Y-%m-01') FROM date_val;
+------------+---------------------------+
| d | DATE_FORMAT(d,'%Y-%m-01') |
+------------+---------------------------+
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
