Recipe 6.7. 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, and day values than year and day-of-year values.

Date synthesis often is performed by beginning with a given date, and 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 and combine them with a day value 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                | | 1987-03-05 | 1987-03-01                | | 1999-12-31 | 1999-12-01                | | 2000-06-04 | 2000-06-01                | +------------+---------------------------+ 

TIME_FORMAT⁠(⁠ ⁠ ⁠) can be used in a similar way. The following example produces time values that have the seconds part set to 00:

mysql> SELECT t1, TIME_FORMAT(t1,'%H:%i:00') FROM time_val; +----------+----------------------------+ | t1       | TIME_FORMAT(t1,'%H:%i:00') | +----------+----------------------------+ | 15:00:00 | 15:00:00                   | | 05:01:30 | 05:01:00                   | | 12:30:20 | 12:30:00                   | +----------+----------------------------+ 

Another way to construct temporal values is to use date-part extraction functions in conjunction with CONCAT⁠(⁠ ⁠ ⁠). However, this method often is messier than the DATE_FORMAT⁠(⁠ ⁠ ⁠) technique just discussed, and it sometimes yields slightly different results:

mysql> SELECT d,     -> CONCAT(YEAR(d),'-',MONTH(d),'-01')     -> FROM date_val; +------------+------------------------------------+ | d          | CONCAT(YEAR(d),'-',MONTH(d),'-01') | +------------+------------------------------------+ | 1864-02-28 | 1864-2-01                          | | 1900-01-15 | 1900-1-01                          | | 1987-03-05 | 1987-3-01                          | | 1999-12-31 | 1999-12-01                         | | 2000-06-04 | 2000-6-01                          | +------------+------------------------------------+ 

Note that the month values in some of these dates have only a single digit. To ensure that the month has two digitsas required for ISO formatuse LPAD⁠(⁠ ⁠ ⁠) to add a leading zero as necessary:

mysql> SELECT d,     -> CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01')     -> FROM date_val; +------------+------------------------------------------------+ | d          | CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') | +------------+------------------------------------------------+ | 1864-02-28 | 1864-02-01                                     | | 1900-01-15 | 1900-01-01                                     | | 1987-03-05 | 1987-03-01                                     | | 1999-12-31 | 1999-12-01                                     | | 2000-06-04 | 2000-06-01                                     | +------------+------------------------------------------------+ 

Section 6.18 shows other ways to solve the problem of producing ISO dates from not-quite-ISO dates.

TIME values can be produced from hours, minutes, and seconds values using methods analogous to those for creating DATE values. For example, to change a TIME value so that its seconds part is 00, extract the hour and minute parts, and then recombine them with CONCAT⁠(⁠ ⁠ ⁠):

mysql> SELECT t1,     -> CONCAT(LPAD(HOUR(t1),2,'0'),':',LPAD(MINUTE(t1),2,'0'),':00')     ->    AS recombined     -> FROM time_val; +----------+------------+ | t1       | recombined | +----------+------------+ | 15:00:00 | 15:00:00   | | 05:01:30 | 05:01:00   | | 12:30:20 | 12:30:00   | +----------+------------+ 

To produce a combined date-and-time value from separate date and time values, simply concatenate them with a space in between:

mysql> SET @d = '2006-02-28'; mysql> SET @t = '13:10:05'; mysql> SELECT @d, @t, CONCAT(@d,' ',@t); +------------+----------+---------------------+ | @d         | @t       | CONCAT(@d,' ',@t)   | +------------+----------+---------------------+ | 2006-02-28 | 13:10:05 | 2006-02-28 13:10:05 | +------------+----------+---------------------+