MySQL TIME 数据类型

---

简介：在本教程中，我们将向您介绍MySQL TIME数据类型，并向您展示有用的时间函数以有效地处理时间数据。

---

MySQL TIME数据类型简介

MySQL使用'HH:MM:SS'格式查询并显示表示一天中24小时内的时间的时间值。为了表示两个事件之间的时间间隔，MySQL使用的'HHH:MM:SS'格式大于24小时。 要定义TIME 列，请使用以下语法：

column_name TIME;

例如，下面的片段定义了一个名为列start_at与TIME数据类型。

start_at TIME;

TIME取值范围为-838:59:59至838:59:59。此外，一个TIME值可以具有小数秒部分，精确到微秒（6位）。要定义其数据类型TIME具有小数秒精度部分的列，请使用以下语法：

column_name TIME(N);

N是一个整数，表示小数部分，最多6位数。 以下代码段定义了一个TIME数据类型包含3位小数秒的列。

begin_at TIME(3);

一个TIME值需要3个字节用于存储。如果TIME值包括小数秒精度，则将根据小数秒精度的位数采用额外的字节。下表说明了小数秒精度所需的存储空间。

分数秒精度	存储（BYTES）
0	0
1,2	1
3,4	2
5,6	3

例如，TIME和TIME(0)占用3个字节。TIME(1)和TIME(2)占用4个字节（3 + 1）; TIME(3)和TIME(6)取5和6个字节。

---

MySQL TIME数据类型示例

让我们看一下在TIME表中使用数据类型的示例。 首先，创建一个新表命名为tests 一个由四列：id，name，start_at，和end_at。start_at和end_at列的数据类型是TIME。

CREATE TABLE tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    start_at TIME,
    end_at TIME
);

其次，在tests表中插入一行。

INSERT INTO tests(name,start_at,end_at)
VALUES('Test 1', '08:00:00','10:00:00');

第三，从tests表中查询数据。

SELECT 
    name, start_at, end_at
FROM
    tests;

+--------+----------+----------+
| name   | start_at | end_at   |
+--------+----------+----------+
| Test 1 | 08:00:00 | 10:00:00 |
+--------+----------+----------+
1 row in set (0.00 sec)

---

MySQL TIME文字

除了'HH:MM:SS'前面提到的格式之外，MySQL还识别各种时间格式。 MySQL允许您使用'HHMMSS'不带分隔符（:) 的格式来表示时间值。例如，'08:30:00'和'10:15:00'可以改写为'083000'和'101500'。

INSERT INTO tests(name,start_at,end_at)
VALUES('Test 2','083000','101500');

mysql> select * from tests;
+----+--------+----------+----------+
| id | name   | start_at | end_at   |
+----+--------+----------+----------+
|  1 | Test 1 | 08:00:00 | 10:00:00 |
|  2 | Test 2 | 08:30:00 | 10:15:00 |
+----+--------+----------+----------+
2 rows in set (0.00 sec)

但是，108000它不是有效的时间值，因为80它不代表正确的分钟。在这种情况下，如果您尝试在表中插入无效的时间值，MySQL将引发错误。

INSERT INTO tests(name,start_at,end_at)
VALUES('Test invalid','083000','108000');

执行上述语句后，MySQL发出以下错误消息。

ERROR 1292 (22007): Incorrect time value: '108000' for column 'end_at' at row 1

除了字符串格式之外，MySQL还接受HHMMSS表示时间值的数字。你也可以用SS，MMSS。例如，'082000'您可以使用082000以下方法，而不是使用它：

INSERT INTO tests(name,start_at,end_at)
VALUES('Test 3',082000,102000);

mysql> select * from tests;
+----+--------+----------+----------+
| id | name   | start_at | end_at   |
+----+--------+----------+----------+
|  1 | Test 1 | 08:00:00 | 10:00:00 |
|  2 | Test 2 | 08:30:00 | 10:15:00 |
|  3 | Test 3 | 08:20:00 | 10:20:00 |
+----+--------+----------+----------+
3 rows in set (0.00 sec)

在时间间隔，则可以使用'D HH:MM:SS'其中的格式D表示天从0到34，一种更为灵活的语法是一个范围'HH:MM'，'D HH:MM'，'D HH'，或'SS'。 如果使用分隔符：，则可以使用1位数表示小时，分钟或秒。例如，使用9:5:0可以代替'09:05:00'。

INSERT INTO tests(name,start_at,end_at)
VALUES('Test 4','9:5:0',100500);

mysql> select * from tests;
+----+--------+----------+----------+
| id | name   | start_at | end_at   |
+----+--------+----------+----------+
|  1 | Test 1 | 08:00:00 | 10:00:00 |
|  2 | Test 2 | 08:30:00 | 10:15:00 |
|  3 | Test 3 | 08:20:00 | 10:20:00 |
|  4 | Test 4 | 09:05:00 | 10:05:00 |
+----+--------+----------+----------+
4 rows in set (0.00 sec)

---

有用的MySQL TIME函数

MySQL为操作TIME数据提供了几个有用的时间函数。

获取当前时间

要获取数据库服务器的当前时间，请使用CURRENT_TIME函数。CURRENT_TIME函数将当前时间值作为字符串（'HH:MM:SS'）或数值（HHMMSS）返回，具体取决于使用该函数的上下文。 以下语句说明了CURRENT_TIME字符串和数字上下文中的函数：

SELECT 
    CURRENT_TIME() AS string_now,
    CURRENT_TIME() + 0 AS numeric_now;

+------------+-------------+
| string_now | numeric_now |
+------------+-------------+
| 15:56:11   |      155611 |
+------------+-------------+
1 row in set (0.00 sec)

从TIME值添加和减去时间

要将TIME值添加到另一个TIME值，请使用该ADDTIME函数。要从TIME另一个TIME值中减去一个值，请使用 SUBTIME函数。 以下语句添加和减去当前时间2小时30分钟。

SELECT 
    CURRENT_TIME(),
    ADDTIME(CURRENT_TIME(), 023000), 
    SUBTIME(CURRENT_TIME(), 023000);

+----------------+---------------------------------+---------------------------------+
| CURRENT_TIME() | ADDTIME(CURRENT_TIME(), 023000) | SUBTIME(CURRENT_TIME(), 023000) |
+----------------+---------------------------------+---------------------------------+
| 15:57:18       | 18:27:18                        | 13:27:18                        |
+----------------+---------------------------------+---------------------------------+
1 row in set (0.00 sec)

此外，您可以使用TIMEDIFF()函数来获取两个TIME值之间的差值。

SELECT 
    TIMEDIFF(end_at, start_at)
FROM
    tests;

+----------------------------+
| TIMEDIFF(end_at, start_at) |
+----------------------------+
| 02:00:00                   |
| 01:45:00                   |
| 02:00:00                   |
| 01:00:00                   |
+----------------------------+
4 rows in set (0.00 sec)

MySQL TIME 格式化

虽然MySQL 'HH:MM:SS'在检索和显示TIME 值时使用，但可以使用TIME_FORMAT函数来格式化TIME值  。 TIME_FORMAT函数与DATE_FORMAT函数类似，只是TIME_FORMAT函数仅用于格式化TIME值。 请参阅以下示例。

SELECT 
    name,
    TIME_FORMAT(start_at, '%h:%i %p') start_at,
    TIME_FORMAT(end_at, '%h:%i %p') end_at
FROM
    tests;

+--------+----------+----------+
| name   | start_at | end_at   |
+--------+----------+----------+
| Test 1 | 08:00 AM | 10:00 AM |
| Test 2 | 08:30 AM | 10:15 AM |
| Test 3 | 08:20 AM | 10:20 AM |
| Test 4 | 09:05 AM | 10:05 AM |
+--------+----------+----------+
4 rows in set (0.00 sec)

在上面的时间格式字符串中：

•  %h 表示从0到12的两位数小时。
•  %i 表示从0到60的两位数分钟。
•  %p AM或PM。

从TIME值中提取小时，分钟和秒

要从TIME中提取小时，分钟和秒值，使用HOUR，MINUTE和SECOND功能如下：

SELECT 
    start_at,
    HOUR(start_at) as h,
    MINUTE(start_at) as m,
    SECOND(start_at) as s
FROM
    tests;

+----------+------+------+------+
| start_at | h    | m    | s    |
+----------+------+------+------+
| 08:00:00 |    8 |    0 |    0 |
| 08:30:00 |    8 |   30 |    0 |
| 08:20:00 |    8 |   20 |    0 |
| 09:05:00 |    9 |    5 |    0 |
+----------+------+------+------+
4 rows in set (0.00 sec)

获取UTC时间值

要获得UTC时间，请使用以下UTC_TIME函数：

SELECT 
   CURRENT_TIME(), 
   UTC_TIME();

+----------------+------------+
| CURRENT_TIME() | UTC_TIME() |
+----------------+------------+
| 16:04:06       | 08:04:06   |
+----------------+------------+
1 row in set (0.00 sec)

在本教程中，我们已经介绍了很多关于MySQL TIME数据类型和一些常用的操作TIME值的时间函数。

---