Skip to main content

掌握MySQL时间数据类型

MySQL时间数据类型简介​

MySQL使用“HH:MM:SS”格式来查询和显示表示一天中某个时间的时间值，该时间值在24小时内。为了表示两个事件之间的时间间隔，MySQL使用了大于24小时的“HHH:MM:SS”格式。

要定义时间列，请使用以下语法：

column_name TIME;

例如，下面的代码段定义了一个名为start_at TIME data type的列。

start_at TIME;

时间值的范围为-838:59:59到838:59:59。此外，时间值可以包含精度高达微秒（6位）的小数秒部分。要定义数据类型为时间且精度为小数秒的列，请使用以下语法：

column_name TIME(N);

N是表示小数部分的整数，最多为6位。

下面的代码段定义了一个时间数据类型为3位小数秒的列。

begin_at TIME(3);

时间值需要3字节的存储空间。如果时间值包含分数秒精度，则需要根据分数秒精度的位数增加字节数。下表说明了分数秒精度所需的存储。

例如，时间和时间（0）需要3个字节。时间（1）和时间（2）需要4个字节（3+1）；时间（3）和时间（6）分别需要5和6个字节。

MySQL时间数据类型示例​

让我们来看一个为表中的列使用时间数据类型的示例。

首先，创建一个名为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;

MySQL TIME example

请注意，我们在INSERT语句中使用'HH:MM:SS'作为文字时间值。让我们检查MySQL能够识别的所有有效时间文本。

MySQL时间文本​

除了我们前面提到的“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 TIME HHMMSS literal

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

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

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

Error Code: 1292. Incorrect time value: '108000' for column 'end_at' at row 1

除了字符串格式之外，MySQL还接受HHMMS作为代表时间值的数字。你也可以使用SS、MMS。例如，您可以按如下方式使用082000，而不是使用“082000”：

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

MySQL TIME HHMMSS numeric

对于时间间隔，您可以使用'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 TIME literals 1 digit

有用的MySQL时间函数​

MySQL提供了几个有用的时间函数来处理时间数据。

了解当前时间​

要获取数据库服务器的当前时间，可以使用current_time函数。CURRENT_TIME函数根据函数使用的上下文，以字符串（'HH:MM:SS'）或数值（HHMMSS）的形式返回当前时间值。

以下语句说明了字符串和数字上下文中的CURRENT_TIME函数：

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

MySQL CURRENT_TIME function

从时间值中加减时间​

要将一个时间值添加到另一个时间值，可以使用ADDTIME函数。要从另一个时间值中减去一个时间值，可以使用SUBTIME函数。

以下语句将当前时间加上或减去2小时30分钟。

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

MySQL ADDTIME SUBTIME example

此外，还可以使用TIMEDIFF（）函数获取两个时间值之间的差值。

SELECT 
    TIMEDIFF(end_at, start_at)
FROM
    tests;

格式化MySQL时间值​

虽然MySQL在检索和显示时间值时使用'HH:MM:SS'，但您可以使用TIME_FORMAT函数以自己喜欢的方式显示时间值。

TIME_FORMAT函数与DATE_FORMAT函数类似，只是TIME_FORMAT函数仅用于格式化时间值。

请参见下面的示例。

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

MySQL TIME_FORMAT function example

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

从时间值中提取小时、分钟和秒​

要从时间值中提取小时、分钟和秒，请使用小时、分钟和秒函数，如下所示：

MySQL HOUR MINUTE SECOND functions

获取UTC时间值​

要获取UTC时间，请使用UTC_time函数，如下所示：

SELECT 
   CURRENT_TIME(), 
   UTC_TIME();

MySQL UTC_TIME

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