限制mysql中的TIME数据类型仅保留到24:00:00 hrs [英] Restrict TIME data type in mysql to hold only till 24:00:00 hrs

问题描述

实际上我使用的是MySql 5.6.4版本，但是问题是MySQL检索并以'hh:mm:ss'格式(或'hhh:mm:ss'格式显示大小时值)显示TIME值

Actually i am using MySql 5.6.4 version.But the problem is that MySQL retrieves and displays TIME values in 'hh:mm:ss' format (or 'hhh:mm:ss' format for large hours values).

TIME值的范围可以从"-838:59:59"到"838:59:59".小时部分可能会很大，因为TIME类型不仅可以用来表示一天中的某个时间(必须少于24小时)，而且可以用来表示经过的时间或两个事件之间的时间间隔(可能很多大于24小时，甚至是负数).

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 5.6.4版本中的TIME数据类型限制为仅保留到24:00:00 hrs，因此如果插入的时间大于24:00:00，那么它将显示错误消息

I want to restrict TIME data type in mysql 5.6.4 version to hold only till 24:00:00 hrs, so if will insert the time which is greater than 24:00:00 then it will show error message.

我为此做了很多搜索，但是虽然有点困惑，但是我什么也没找到...我只想知道我该怎么办?

I have googled a lot for this but i didn'yt find anything although i am bit confused... I just want to know How can i do it?

Insert into exp1.mock_table (reviewTime, created_dt) VALUES ('37:00:00', NOW());

Insert into exp1.mock_table (reviewTime, created_dt) VALUES ('22:01:53', NOW());

alter table exp1.mock_table modify reviewTime time(0) , add check (extract(hour from reviewTime) between 0 and 24);

推荐答案

请考虑以下内容:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (id SERIAL PRIMARY KEY, t TIME NOT NULL);

INSERT INTO my_table (t) SELECT '22:23:00' FROM (SELECT 1) n WHERE TIME_TO_SEC('22:23:00') BETWEEN 0 AND 86400;

INSERT INTO my_table (t) SELECT '212:23:00' FROM (SELECT 1) n WHERE TIME_TO_SEC('212:23:00') BETWEEN 0 AND 86400;

SELECT * FROM my_table;
+----+----------+
| id | t        |
+----+----------+
|  1 | 22:23:00 |
+----+----------+

这篇关于限制mysql中的TIME数据类型仅保留到24:00:00 hrs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！