MySQL retrieves and displays
'HHH:MM:SS'format for large hours values).
TIMEvalues may range from
'838:59:59'. The hours part may be so large because the
TIMEtype 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
TIMEvalues 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:
'D HH', or
Drepresents 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
'109712'is illegal (it has a nonsensical minute part) and becomes
As a number in
HHMMSSformat, provided that it makes sense as a time. For example,
101112is understood as
'10:11:12'. The following alternative formats are also understood:
HHMMSS.fraction. Note that MySQL does not store the fraction part.
.uuuuuumicroseconds part of
TIMEvalues is allowed under the same conditions as for other temporal values, as described in Section 10.3.1, “The
TIMESTAMPTypes”. This includes the property that any microseconds part is discarded from values stored into
TIMEvalues 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
'8:3:2'is the same as
Be careful about assigning abbreviated values to a
TIMEcolumn. Without colons, MySQL interprets values using the assumption that the two rightmost digits represent seconds. (MySQL interprets
TIMEvalues as elapsed time rather than as time of day.) For example, you might think of
'11:12:00'(12 minutes after 11 o'clock), but MySQL interprets them as
'00:11:12'(11 minutes, 12 seconds). Similarly,
12are interpreted as
TIMEvalues with colons, by contrast, are always treated as time of the day. That is,
By default, values that lie outside the
TIMErange but are otherwise legal are clipped to the closest endpoint of the range. For example,
'850:00:00'are converted to
TIMEvalues are converted to
'00:00:00'. Note that because
'00:00:00'is itself a legal
TIMEvalue, 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.