全部教學 » MySQL time型別

MySQL time型別

2019-10-16 22:58:36

在本教學中，我們將向您介紹MySQL TIME資料型別，並顯示有用的時間函式來有效地處理時間資料。

MySQL TIME資料型別簡介

MySQL使用HH:MM:SS格式來查詢和顯示代表一天中的時間值(在24小時內)。要表示兩個事件之間的時間間隔，MySQL使用大於24小時的HHH:MM:SS格式。

要定義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和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。

USE testdb;

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;

執行上面查詢語句，得到以下結果 -

請注意，我們使用「HH:MM:SS」作為INSERT語句中的文字時間值。下面來看看MySQL可以識別的所有有效的時間文字。

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');

但是，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);

對於時間間隔，您可以使用'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函式

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 |
+------------+-------------+
| 23:04:53   |      230453 |
+------------+-------------+
1 row in set

從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) |
+----------------+---------------------------------+---------------------------------+
| 23:05:03       | 25:35:03                        | 20:35:03                        |
+----------------+---------------------------------+---------------------------------+
1 row in set

此外，可以使用TIMEDIFF()函式來獲取兩個TIME值之間的差異。

SELECT 
    TIMEDIFF(end_at, start_at)
FROM
    tests;

執行上面查詢語句，得到以下結果 - 

mysql> 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

格式化MySQL TIME值

雖然MySQL在檢索和顯示TIME值時使用「HH:MM:SS」，但可以使用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

在上面的時間格式字串中：

  • ％h表示從0到12的兩位數小時數值。
  • ％i表示從0到60的兩位數分鐘數值。
  • ％p表示AM或PM，也就是表示上午或下午。

從TIME值提取小時，分鐘和秒

要從TIME值中提取小時，分和秒，可以使用HOUR，MINUTE和SECOND函式，如下所示：

獲取UTC時間值

要獲取UTC時間，請使用UTC_TIME函式，如下所示：

SELECT 
   CURRENT_TIME(), 
   UTC_TIME();

執行上面查詢語句，得到以下結果 - 

+----------------+------------+
| CURRENT_TIME() | UTC_TIME() |
+----------------+------------+
| 23:05:41       | 15:05:41   |
+----------------+------------+
1 row in set

在本教學中，我們已經介紹了有關MySQL TIME資料型別和一些常用的時間函式來處理TIME值。