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 8.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 10.3.4, “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.
© 2012, Oracle Corporation and/or its affiliates
User Comments
If you want to change the default output format you can convert to string with TIME_FORMAT(). E.g.:
SELECT TIME_FORMAT(foo_hour, '%H:%i')
FROM bar
Add your own comment.