MySQL/MariaDB 資料庫 INTERVAL 日期、時間間隔用法教學與範例

介紹如何在 MySQL/MariaDB 資料庫中使用 INTERVAL 表示時間間隔，進行時間運算。

INTERVAL 時間間隔

在 MySQL/MariaDB 資料庫中若需要表示一段時間間隔時，可以使用 INTERVAL，其語法如下：

INTERVAL 間隔值 單位

若要表示間隔一天的時間，則可以這樣寫：

INTERVAL 1 DAY

例如若要計算 2019/10/03 的 100 天後是哪一天，就可以這樣寫：

# 計算 2019/10/03 的 100 天後是哪一天
SELECT '2019-10/03' + INTERVAL 100 DAY;

+---------------------------------+
| '2019-10/03' + INTERVAL 100 DAY |
+---------------------------------+
| 2020-01-11                      |
+---------------------------------+

也可以配合 DATE_ADD 函數使用：

# 計算 2019/10/03 的 100 天後是哪一天
SELECT DATE_ADD('2019-10-03', INTERVAL 100 DAY);

+------------------------------------------+
| DATE_ADD('2019-10-03', INTERVAL 100 DAY) |
+------------------------------------------+
| 2020-01-11                               |
+------------------------------------------+

若要計算 2019/10/03 的 100 天前是哪一天，則可以這樣寫：

# 計算 2019/10/03 的 100 天前是哪一天
SELECT '2019-10/03' - INTERVAL 100 DAY;

+---------------------------------+
| '2019-10/03' - INTERVAL 100 DAY |
+---------------------------------+
| 2019-06-25                      |
+---------------------------------+

# 計算 2019/10/03 的 100 天前是哪一天
SELECT DATE_SUB('2019-10-03', INTERVAL 100 DAY);

+------------------------------------------+
| DATE_SUB('2019-10-03', INTERVAL 100 DAY) |
+------------------------------------------+
| 2019-06-25                               |
+------------------------------------------+

時間單位與間隔值

INTERVAL 可用的時間格式非常多，以下是各種可用的時間單位與間隔值：

若要計算 2019/10/03 10:45:35 過了 25 分 46 秒之後的時間點，可以這樣寫：

# 計算 2019/10/03 10:45:35 過了 25 分 46 秒的時間點
SELECT '2019-10-03 10:45:35' + INTERVAL '25:46' MINUTE_SECOND;

若要計算從現在算起，3 天 5 小時 45 分鐘之後的時間點：

# 從現在算起 3 天 5 小時 45 分鐘之後的時間點
SELECT NOW() + INTERVAL '3 5:45' DAY_MINUTE;

+--------------------------------------+
| NOW() + INTERVAL '3 5:45' DAY_MINUTE |
+--------------------------------------+
| 2019-12-06 06:22:09                  |
+--------------------------------------+

實際應用範例

假設我們要儲存一些具有使用期限的帳號資料，首先建立含有有效期限欄位的帳號資料表：

# 建立帳號資料表
CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,   # 帳號名稱
    expired_date DATE NOT NULL       # 有效期限
);

在新增帳號時，可以直接指定帳號的到期日，或是運用 INTERVAL 根據使用時間計算到期日：

# 新增帳號資料
INSERT INTO accounts (username, expired_date)
VALUES('Marie', '2020-07-13'),
      ('Lucas', '2019-12-10'),
      ('Robert', NOW() + INTERVAL 3 MONTH),        # 三個月之後到期
      ('Jack', NOW() + INTERVAL '1-6' YEAR_MONTH); # 一年六個月之後到期

若要列出所有帳號剩餘的使用天數，可以運用 DATEDIFF 函數：

# 列出所有帳號有效天數
SELECT *, DATEDIFF(expired_date, NOW()) remaining_days FROM accounts;

+----+----------+--------------+----------------+
| id | username | expired_date | remaining_days |
+----+----------+--------------+----------------+
|  1 | Marie    | 2020-07-13   |            223 |
|  2 | Lucas    | 2019-12-10   |              7 |
|  3 | Robert   | 2020-03-03   |             91 |
|  4 | Jack     | 2021-06-03   |            548 |
+----+----------+--------------+----------------+

若要查詢即將到期的帳號，可以運用 BETWEEN 以及 INTERVAL 來判斷：

# 查詢兩週內到期的帳號
SELECT * FROM accounts WHERE expired_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY);

+----+----------+--------------+
| id | username | expired_date |
+----+----------+--------------+
|  2 | Lucas    | 2019-12-10   |
+----+----------+--------------+

參考文件：MySQL 官方文件、易百教程