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).
You can specify TIME
values in a
variety of formats:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction'
,
'HH:MM:SS'
, 'HH:MM'
,
'D HH:MM:SS'
, 'D
HH:MM'
, 'D HH'
, or
'SS'
. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not store the fraction part.
As a string with no delimiters in
'HHMMSS'
format, provided that it makes
sense as a time. For example, '101112'
is
understood as '10:11:12'
, but
'109712'
is illegal (it has a nonsensical
minute part) and becomes '00:00:00'
.
As a number in HHMMSS
format, provided
that it makes sense as a time. For example,
101112
is understood as
'10:11:12'
. The following alternative
formats are also understood: SS
,
MMSS
, HHMMSS
,
HHMMSS.fraction
. Note that MySQL does not
store the fraction part.
As the result of a function that returns a value that is
acceptable in a TIME
context,
such as CURRENT_TIME
.
A trailing .uuuuuu
microseconds part of
TIME
values is allowed under the
same conditions as for other temporal values, as described in
Section 10.3.1, “The DATETIME
,
DATE
, and
TIMESTAMP
Types”. This includes the property that any
microseconds part is discarded from values stored into
TIME
columns.
For TIME
values specified as
strings that include a time part delimiter, it is not necessary
to specify two digits for hours, minutes, or seconds values that
are less than 10
. '8:3:2'
is the same as '08:03:02'
.
Be careful about assigning abbreviated values to a
TIME
column. Without colons,
MySQL interprets values using the assumption that the two
rightmost digits represent seconds. (MySQL interprets
TIME
values 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'
.
TIME
values with colons, by
contrast, are always treated as time of the day. That is,
'11:12'
mean '11:12:00'
,
not '00:11: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.8, “Server SQL Modes”.
User Comments
Add your own comment.