Introduction

MySQL has several data types for representing dates and times, and several functions for operating on them. MySQL stores dates and times in specific formats. It's important to understand them to avoid surprises in how MySQL interprets input data. MySQL also has reformatting functions for producing date and time output in formats other than the default. This chapter covers the following aspects of working with temporal values in MySQL:

• Displaying dates and times.

  MySQL displays temporal values using specific formats by default, but you can produce other formats by calling the appropriate function.

• Determining the current date or time.

  MySQL provides functions that return the date and time, which is useful for applications that need to know these values or need to calculate other temporal values in relation to them.

• Decomposing dates or times into component values.

  This section explains how to split date and time values when you need only a piece, such as the month part or the hour part.

• Synthesizing dates and times from component values.

  The complement of splitting apart temporal values is to create them from subparts. This section shows how.

• Converting between dates or times and basic units.

  Some date calculations are more easily performed using the number of days or seconds represented by a date or time value than by using the value itself. MySQL makes it possible to perform several kinds of conversions between date and time values and more basic units such as days or seconds. These conversions often are useful for interval calculations (such as time elapsed between two times).

• Date and time arithmetic.

  It's possible in MySQL to add temporal intervals to date or time values to produce other dates or times, and to calculate the interval between dates or times. Time arithmetic is easier than date arithmetic. Times involve hours, minutes, and secondsunits that always have a fixed duration. Date arithmetic can be trickier because units such as months and years vary in length.

• Applications for date and time arithmetic.

  Using the techniques from the earlier sections, this one shows how to perform age calculation, relative date computation, date shifting, and leap year calculation.

• Selecting records based on temporal constraints.

  The calculations discussed in the preceding sections to produce output values can also be used in WHERE clauses to specify how to select records using temporal conditions.

• Using TIMESTAMP values.

  The TIMESTAMP column type has some special properties that make it convenient for automatically recording record creation and modification times. This section describes how TIMESTAMP columns behave and how to use them. It also discusses how to display TIMESTAMP values in more readable formats.

This chapter covers many of MySQL's functions for operating on date and time values, but there are yet others. To familiarize yourself with the full set, consult the MySQL Reference Manual. The variety of functions available to you means that it's often possible to perform a given temporal calculation more than one way. I sometimes illustrate alternative methods for achieving a given result, but many of the problems addressed in this chapter can be solved in other ways than are shown here. I invite you to experiment to find other solutions. You may find a method that's more efficient or that you find more readable.

Scripts that implement the recipes discussed in this chapter can be found in the dates directory of the recipes source distribution. The scripts that create the tables used here are located in the tables directory.

5.1.1 MySQL's Date and Time Formats

MySQL provides DATE and TIME column types for representing date and time values separately, and a DATETIME type for combined date-and-time values. These values have the following formats:

• DATE values are handled as strings in CCYY-MM-DD format, where CC, YY, MM, and DD represent the century, year within century, month, and day parts of the date.
• TIME values are represented as strings in hh:mm:ss format, where hh, mm, and ss are the hours, minutes, and seconds parts of the time. TIME values often can be thought of as time-of-day values, but MySQL actually treats them as elapsed time. Thus, they may be greater than 23:59:59 or even negative. (The actual range is -838:59:59 to 838:59:59.)
• DATETIME values are represented as combined date-and-time strings in CCYY-MM-DD hh:mm:ss format.
• TIMESTAMP values also include date and time parts, but are represented as strings in CCYYMMDDhhmmss format. This column type also has special properties that are discussed further in Recipe 5.32. More examples in this chapter use DATETIME values than TIMESTAMP values (which are less readable), but in most respects, you can treat the two column types the same way.

Many of the examples in this chapter draw on the following tables, which contain columns representing TIME, DATE, DATETIME, and TIMESTAMP values. (The time_val table has two columns for use in time interval calculation examples.)

mysql> SELECT t1, t2 FROM time_val; +----------+----------+ | t1 | t2 | +----------+----------+ | 15:00:00 | 15:00:00 | | 05:01:30 | 02:30:20 | | 12:30:20 | 17:30:45 | +----------+----------+ mysql> SELECT d FROM date_val; +------------+ | d | +------------+ | 1864-02-28 | | 1900-01-15 | | 1987-03-05 | | 1999-12-31 | | 2000-06-04 | +------------+ mysql> SELECT dt FROM datetime_val; +---------------------+ | dt | +---------------------+ | 1970-01-01 00:00:00 | | 1987-03-05 12:30:15 | | 1999-12-31 09:00:00 | | 2000-06-04 15:45:30 | +---------------------+ mysql> SELECT ts FROM timestamp_val; +----------------+ | ts | +----------------+ | 19700101000000 | | 19870305123015 | | 19991231090000 | | 20000604154530 | +----------------+