5.1 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
seconds—units 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 |
+----------------+
|
Main Menu
|
