• 跳至主要導覽
  • 跳至主要內容
  • 跳至主要資訊欄
Office 指南

Office 指南

辦公室工作實用教學

  • Excel
  • Word
  • PowerPoint
  • Windows
  • PowerShell
  • R

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 可用的時間格式非常多,以下是各種可用的時間單位與間隔值:

單位 間隔值
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 官方文件、易百教程

分類:資料庫 標籤:MySQL/MariaDB

讀者互動方式

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

主要資訊欄

搜尋

近期文章

  • C++ 語言使用 Crypto++ 實作 RSA 數位簽章教學與範例
  • C++ 語言使用 Crypto++ 實作 RSA-OAEP 搭配 SHA256 加密教學與範例
  • C++ 語言使用 Crypto++ 實作 AES 加密、解密、認證加密教學與範例
  • C++ 語言使用 Crypto++ 實作 MD5、SHA1、SHA2、BLAKE2 雜湊教學與範例
  • Ubuntu Linux 安裝、使用 Crypto++ 加密函式庫教學與範例
  • C 語言使用 OpenSSL 實作橢圓曲線 ECDH 金鑰交換教學與範例
  • Python 以 eciespy 實作 ECC 非對稱式加密方法教學與範例
  • C 語言使用 OpenSSL 實作 PBKDF2 教學與範例

推薦網站

  • Udemy 線上教學課程
  • Coursera 線上教學課程

關注本站

  • 電子郵件
  • Facebook

公益

  • 家扶基金會
  • 台灣世界展望會
  • Yahoo 奇摩公益
  • igiving 公益網
  • 兒福聯盟

Copyright © 2021 · Office Guide