Choose the data type according to the characteristics of the information to be stored and how you need to use it. 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 separately, and
DATETIME
and
TIMESTAMP
types for combined
date-and-time values. These values have the following
characteristics:
DATE
values are handled
as strings inCCYY-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 are
represented as strings inhh: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
values are represented as
combined date-and-time strings in
CCYY-MM-DD
hh:mm:ss
format. (Before MySQL 4.1,
TIMESTAMP
display format was
CCYYMMDDhhmmss
numeric format. Older
applications that depend on this display format must be updated
for MySQL 4.1 and up.)
In many respects, you can treat the DATETIME
and TIMESTAMP
data types similarly, 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 to approximately 2037.
The TIMESTAMP
type
has special auto-initialization and auto-update properties
that are discussed further in Using TIMESTAMP to Track Row Modification Times.
When a client inserts a TIMESTAMP
value, the server converts
it from the time zone associated with the client connection 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 connection time zone. Clients that are in
different time zones from the server can configure their
connection so that this conversion is appropriate for their
own time zone (Setting the Client Time Zone).
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 | +---------------------+ | 1970-01-01 00:00:00 | | 1987-03-05 12:30:15 | | 1999-12-31 09:00:00 | | 2000-06-04 15:45:30 | +---------------------+
It is a good idea to create the time_val
, date_val
, datetime_val
, and timestamp_val
tables right now before
reading further. (Use the appropriate scripts in the tables directory of the recipes
distribution.)
3.147.6.10