MySQL Cookbook, 2nd Edition

Synthesizing Dates or Times from Component Values

Problem

You want to produce a new date from a given date by replacing parts of its values. Or you have the parts of a date or time and want to combine them to produce a date or time value.

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 recombine 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 arguments 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         |
+--------------------+-------------------+

There is also a MAKEDATE() function, but its arguments are year and day-of-year values:

mysql>SELECT MAKEDATE(2007,60);
+-------------------+
| MAKEDATE(2007,60) |
+-------------------+
| 2007-03-01        |
+-------------------+

I don’t find MAKEDATE() very useful because I’m much more likely to be working with year, month, ...