Database Reference
In-Depth Information
Discussion
To choose a temporal data type, consider questions such as these:
• Do you need times only, dates only, or combined date and time values?
• What range of values do you require?
• Do you want automatic initialization of the column to the current date and time?
MySQL provides
DATE
and
TIME
data types for representing date and time values sepa‐
rately, and
DATETIME
and
TIMESTAMP
types for combined date-and-time values. These
values have the following characteristics:
•
DATE
values have
CCYY-MM-DD
format, where
CC
,
YY
,
MM
, and
DD
represent the century,
year within century, month, and day parts of the date. The supported range for
DATE
values is
1000-01-01
to
9999-12-31
.
•
TIME
values have
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 of a
TIME
column is
-838:59:59
to
838:59:59
.)
•
DATETIME
and
TIMESTAMP
are combined date-and-time values in
CCYY-MM-DD
hh:mm:ss
format.
The
DATETIME
and
TIMESTAMP
data types are similar in many respects, but watch
out for these differences:
—
DATETIME
has a supported range of
1000-01-01 00:00:00
to
9999-12-31
23:59:59
, whereas
TIMESTAMP
values are valid only from the year 1970 partially
through 2038.
—
TIMESTAMP
and
DATETIME
have special auto-initialization and auto-update prop‐
erties (see
Recipe 6.7
), but for
DATETIME
they are not available before MySQL
5.6.5.
— When a client inserts a
TIMESTAMP
value, the server converts it from the time
zone associated with the client session to UTC and stores the UTC value. When
the client retrieves a
TIMESTAMP
value, the server performs the reverse operation
to convert the UTC value back to the client session time zone. A client in a time
zone different from the server can configure its session so that this conversion
is appropriate for its own time zone (see
Recipe 6.4
).
• Types that include a time part can have a fractional seconds part for subsecond
resolution (see
Recipe 6.2
).