Date and Time Types
841
time that a table is created,
TIMESTAMP
columns are created as
DATETIME
columns. As a result, such columns use
DATETIME
display format, have the
same range of values, and there is no automatic initialization or updating to the
current date and time. See
Section 5.1.7, “Server SQL Modes”
.
11.1.5.2. The
TIME
Type
MySQL retrieves and displays
TIME
values in
'HH:MM:SS'
format (or
'HHH:MM:SS'
format for large
hours values).
TIME
values may range from
'-838:59:59'
to
'838:59:59'
. The hours part may
be so large because the
TIME
type can be used not only to represent a time of day (which must be
less than 24 hours), but also elapsed time or a time interval between two events (which may be much
greater than 24 hours, or even negative).
MySQL recognizes
TIME
values in several formats, described in
Section 9.1.3, “Date and Time
Literals”
. Some of these formats can include a trailing fractional seconds part in up to microseconds (6
digits) precision. Although this fractional part is recognized, it is discarded from values stored into
TIME
columns. For information about fractional seconds support in MySQL, see
Section 11.1.5.6, “Fractional
Seconds in Time Values”
.
Be careful about assigning abbreviated values to a
TIME
column. MySQL interprets abbreviated
TIME
values with colons as time of the day. That is,
'11:12'
means
'11:12:00'
, not
'00:11:12'
.
MySQL interprets abbreviated values without colons using the assumption that the two rightmost digits
represent seconds (that is, as elapsed time rather than as time of day). For example, you might think of
'1112'
and
1112
as meaning
'11:12:00'
(12 minutes after 11 o'clock), but MySQL interprets them
as
'00:11:12'
(11 minutes, 12 seconds). Similarly,
'12'
and
12
are interpreted as
'00:00:12'
.
By default, values that lie outside the
TIME
range but are otherwise legal are clipped to the
closest endpoint of the range. For example,
'-850:00:00'
and
'850:00:00'
are converted to
'-838:59:59'
and
'838:59:59'
. Illegal
TIME
values are converted to
'00:00:00'
. Note that
because
'00:00:00'
is itself a legal
TIME
value, there is no way to tell, from a value of
'00:00:00'
stored in a table, whether the original value was specified as
'00:00:00'
or whether it was illegal.
For more restrictive treatment of invalid
TIME
values, enable strict SQL mode to cause errors to occur.
See
Section 5.1.7, “Server SQL Modes”
.
11.1.5.3. The
YEAR
Type
The
YEAR
type is a 1-byte type used to represent year values. It can be declared as
YEAR(4)
or
YEAR(2)
to specify a display width of four or two characters. The default is four characters if no width
is given.
Note
The
YEAR(2)
data type has certain issues that you should consider before
choosing to use it. For more information, see
Section 11.1.5.4, “
YEAR(2)
Limitations and Migrating to
YEAR(4)
”
.
YEAR(4)
and
YEAR(2)
differ in display format, but have the same range of values. For 4-digit format,
MySQL displays
YEAR
values in
YYYY
format, with a range of
1901
to
2155
, or
0000
. For 2-digit
format, MySQL displays only the last two (least significant) digits; for example,
70
(1970 or 2070) or
69
(2069).
You can specify input
YEAR
values in a variety of formats:
• As a 4-digit string in the range
'1901'
to
'2155'
.
• As a 4-digit number in the range
1901
to
2155
.
• As a 1- or 2-digit string in the range
'0'
to
'99'
. Values in the ranges
'0'
to
'69'
and
'70'
to
'99'
are converted to
YEAR
values in the ranges
2000
to
2069
and
1970
to
1999
.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
