介紹如何在 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 可用的時間格式非常多，以下是各種可用的時間單位與間隔值：
| 單位 | 間隔值 |
|---|---|
MICROSECOND |
MICROSECONDS |
SECOND |
SECONDS |
MINUTE |
MINUTES |
HOUR |
HOURS |
DAY |
DAYS |
WEEK |
WEEKS |
MONTH |
MONTHS |
QUARTER |
QUARTERS |
YEAR |
YEARS |
SECOND_MICROSECOND |
'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
'MINUTES:SECONDS' |
HOUR_MICROSECOND |
'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
'HOURS:MINUTES' |
DAY_MICROSECOND |
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
DAY_SECOND |
'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
'DAYS HOURS:MINUTES' |
DAY_HOUR |
'DAYS HOURS' |
YEAR_MONTH |
'YEARS-MONTHS' |
若要計算 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 官方文件、易百教程
發佈留言