在 MySQL 中使用 Time 数据类型而无需秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3027194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Time datatype in MySQL without seconds
提问by Alex
I'm trying to store a 12/24hr (ie; 00:00) clock time in a MySQL database. At the moment I am using the time datatype. This works ok but it insists on adding the seconds to the column. So you enter 09:20 and it is stored as 09:20:00. Is there any way I can limit it in MySQL to just 00:00?
我正在尝试在 MySQL 数据库中存储 12/24 小时（即 00:00）时钟时间。目前我正在使用时间数据类型。这工作正常，但它坚持将秒添加到列中。因此，您输入 09:20 并将其存储为 09:20:00。有什么办法可以将 MySQL 中的时间限制为 00:00？
回答by Daniel Vassallo
That doesn't look possible. The TIMEdata type is defined to represent the time of the day (or elapsed time) with a 1 second resolution. However, you can always use the DATE_FORMAT()function to format your field as HH:MMin a SELECTquery:
那看起来不太可能。该TIME数据类型被定义为表示当天（或经过时间）的与分辨率1秒的时间。但是，您始终可以使用该DATE_FORMAT()函数来格式化您的字段，就像HH:MM在SELECT查询中一样：
SELECT DATE_FORMAT(NOW(), '%k:%i');
+-----------------------------+
| DATE_FORMAT(NOW(), '%k:%i') |
+-----------------------------+
| 4:09 |
+-----------------------------+
1 row in set (0.00 sec)
SELECT DATE_FORMAT(NOW(), '%H:%i');
+-----------------------------+
| DATE_FORMAT(NOW(), '%H:%i') |
+-----------------------------+
| 04:09 |
+-----------------------------+
1 row in set (0.00 sec)
回答by álvaro González
The TIMEcolumn typedoes not accept any parameter or modifier to define range or precision. You can, however, omit seconds on insert if you are careful:
该TIME列的类型不接受任何参数或改性剂，以限定范围或精度。但是，如果您小心，您可以在插入时省略秒数：
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 1112as 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”。
CREATE TABLE example (
example_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
elapsed TIME NOT NULL,
PRIMARY KEY (example_id)
);
INSERT INTO example (elapsed) VALUES ('123:45:00'), ('123:45');
SELECT * FROM example;
+------------+-----------+
| example_id | elapsed |
+------------+-----------+
| 1 | 123:45:00 |
| 2 | 123:45:00 |
+------------+-----------+
... and you can remove them on read (if necessary) by applying a proper TIME_FORMAT(), noting that:
...并且您可以通过应用适当的TIME_FORMAT()在读取时删除它们（如有必要），请注意：
If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.
如果时间值包含大于 23 的小时部分，则 %H 和 %k 小时格式说明符生成的值大于通常的 0..23 范围。其他小时格式说明符产生模 12 的小时值。
INSERT INTO example (elapsed) VALUES ('2:00');
SELECT example_id, TIME_FORMAT(elapsed, '%k:%i') AS elapsed
FROM example;
+------------+---------+
| example_id | elapsed |
+------------+---------+
| 1 | 123:45 |
| 2 | 123:45 |
| 3 | 2:00 |
+------------+---------+
Since MySQL/5.7.5 you can also use a generated columnto get a display value automatically:
从 MySQL/5.7.5 开始，您还可以使用生成的列自动获取显示值：
-- Completely untested, I don't have 5.7 yet
CREATE TABLE example (
example_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
elapsed TIME NOT NULL,
-- Size to accomodate for '-838:59:59'
elapsed_display VARCHAR(10) AS (TIME_FORMAT(elapsed, '%k:%i')) VIRTUAL NOT NULL,
PRIMARY KEY (example_id)
);
