掌握 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 值包含小数秒精度，它将根据小数秒精度的位数占用额外的字节。下表说明了小数秒精度所需的存储空间。

| 分数秒精度 | 存储（字节） | | 0 | 0 | | 1、2 | 1 | | 3、4 | 2 | | 5、6 | 3 |

例如， TIME and 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;

MySQL 时间实例
MySQL 时间实例

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

MySQL 时间文字

除了 'HH:MM:SS' 我们之前提到的格式之外，MySQL 还可以识别各种时间格式。

MySQL 允许您使用 'HHMMSS' 不带分隔符 (:)的格式来表示时间值。例如， '08:30:00' and '10:15:00' 可以改写为 '083000' and '101500' 。

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

MySQL TIME HHMMSS 文字
MySQL TIME HHMMSS 文字

但是， 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 还接受 HHMMSS 表示时间值的数字。您也可以使用 SS , MMSS 。例如， '082000' 您可以使用 082000 如下代替使用：

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

MySQL TIME HHMMSS 数字
MySQL TIME HHMMSS 数字

在时间间隔，则可以使用 '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 文字 1 位
MySQL TIME 文字使用 1 位数字

有用的 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;

MySQL CURRENT_TIME 函数

从 TIME 值中添加和减去时间

要将一个 TIME 值添加到另一个 TIME 值，请使用该 ADDTIME 函数。要从 TIME 另一个 TIME 值中减去一个值，请使用该 SUBTIME 函数。

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

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

MySQL ADDTIME SUBTIME 实例
MySQL ADDTIME SUBTIME 实例

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

SELECT 
    TIMEDIFF(end_at, start_at)
FROM
    tests;

格式化 MySQL TIME 值

尽管 MySQL 'HH:MM:SS' 在检索和显示 a TIME 值时使用，但您可以 TIME 使用该 TIME_FORMAT 函数以您喜欢的方式显示该 值 。

该 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;

MySQL TIME_FORMAT 函数实例
MySQL TIME_FORMAT 函数实例

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

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

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

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

MySQL HOUR MINUTE SECOND 函数
MySQL HOUR MINUTE SECOND 函数

获取UTC时间值

要获取 UTC 时间，请使用 UTC_TIME 如下函数：

SELECT 
   CURRENT_TIME(), 
   UTC_TIME();

MySQL UTC_TIME
MySQL UTC_TIME 函数

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