Database Reference
In-Depth Information
Breaking dates and times into component values, creating dates and times from compo‐
nent values
You can split date and time values when you need only a component, such as the
month part of a date or the hour part of a time. Conversely, you can combine
component values to synthesize dates and times.
Converting between dates or times and basic units
Some temporal calculations such as date arithmetic operations are more easily per‐
formed using the number of days or seconds represented by a date or time value
than by using the value itself. MySQL can perform conversions between date and
time values and more basic units such as days or seconds.
Date and time arithmetic
You can add or subtract temporal values to produce other temporal values or cal‐
culate intervals between values. Applications include age determination, relative
date computation, and date shifting.
Selecting data 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 rows using temporal condi‐
tions.
This chapter covers several MySQL functions for operating on date and time values, but
there are many 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 al‐
ternative methods for achieving a given result, and many of the problems addressed in
this chapter can be solved in ways other than 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
intuitive.
Scripts that implement recipes discussed in this chapter are located in the
dates
directory
of the
recipes
source distribution. Scripts that create tables used here are located in the
tables
directory.
6.1. Choosing a Temporal Data Type
Problem
You need to store temporal data but aren't sure which is the most appropriate data type.
Solution
Choose the data type according to the characteristics of the information to be stored
and how you need to use it.
