(mysql.info) time
Info Catalog
(mysql.info) datetime
(mysql.info) date-and-time-types
(mysql.info) year
11.3.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).
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'.
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 server-sql-mode.
Info Catalog
(mysql.info) datetime
(mysql.info) date-and-time-types
(mysql.info) year
automatically generated byinfo2html