MySQL 5.7-11.2.3 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).

MySQL以“hh:mm:ss”格式(或者“hhh:mm:ss”格式)检索并显示TIME值。TIME的取值范围为“-838:59:59”到“838:59:59”。小时的部分可能是如此之大,因为可以使用时间类型不仅代表一个时间(必须小于24小时),而且运行时间或两个事件之间的时间间隔(这可能是远远大于24小时,甚至负)。

MySQL recognizes TIME values in several formats, some of which can include a trailing fractional seconds part in up to microseconds (6 digits) precision. See Section 9.1.3, “Date and Time Literals”. For information about fractional seconds support in MySQL, see Section 11.2.7, “Fractional Seconds in Time Values”. In particular, any fractional part in a value inserted into a TIME column is stored rather than discarded. With the fractional part included, the range for TIME values is '-838:59:59.000000' to '838:59:59.000000'.

MySQL可以识别几种格式的TIME值，其中一些格式可以包含一个尾随的小数秒，精度可达微秒(6位)。参见第9.1.3节“日期和时间文字”。关于MySQL中对分数秒的支持，请参见11.2.7节“时间值中的分数秒”。特别是，插入到TIME列的值中的任何小数部分都将被存储，而不是丢弃。如果包含小数部分，TIME的取值范围是'-838:59:59 000000'到'838:59:59 000000'。

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'.

在给TIME列赋缩位值时要小心。MySQL将带有冒号的TIME值缩写为一天中的时间。也就是说，11:12的意思是11:12:00，而不是00:11:12。MySQL在解释没有冒号的缩写值时，假设最右边的两个数字表示秒(也就是说，表示经过的时间而不是一天中的时间)。例如，您可能认为'1112'和1112的意思是'11:12:00'(11点后的12分钟)，但MySQL将它们解释为'00:11:12'(11分12秒)。类似地，'12'和12被解释为'00:00:12'。

The only delimiter recognized between a time part and a fractional seconds part is the decimal point.

在时间部分和小数秒部分之间识别的唯一分隔符是小数点。

By default, values that lie outside the TIME range but are otherwise valid 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'. Invalid TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a valid 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 invalid.

默认情况下，位于TIME范围之外但在其他方面有效的值将被裁剪到该范围中最近的端点。例如，'-850:00:00'和'850:00:00'会转换为'-838:59:59'和'838:59:59'。无效的TIME值将被转换为“00:00:00”。注意，由于'00:00:00'本身是一个有效的TIME值，因此无法从存储在表中的'00:00:00'值判断原始值是指定为'00:00:00'还是无效的。

For more restrictive treatment of invalid TIME values, enable strict SQL mode to cause errors to occur. See Section 5.1.10, “Server SQL Modes”.

要对无效的TIME值进行更严格的处理，请启用严格的SQL模式以导致错误发生。