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'. HereDrepresents 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
HHMMSSformat, provided that it makes sense as a time. For example,101112is 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
TIMEcontext, such asCURRENT_TIME.
A trailing .uuuuuu microseconds part of
TIME values is permitted 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.7, “Server SQL Modes”.