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时间数据类型和一些常用的用于操作时间值的时态函数的内容。