SQL 日期和時間

在本教程中，你將學習如何在 SQL 中使用日期和時間。

日期和時間操縱

除了字串和數字之外，你還經常需要在資料庫中儲存日期和/或時間值，例如使用者的出生日期，員工的招聘日期，未來事件的日期，表中特定行的建立或修改日期和時間，等等。

這種型別的資料稱為時態資料，每個資料庫引擎都有預設的儲存格式和用於儲存它們的資料型別。下表顯示了 MySQL 資料庫伺服器支援的用於處理日期和時間的資料型別。

DATE 值具有 YYYY-MM-DD 格式，其中 YYYY 表示全年（4 位數），而 MM 和 DD 表示日期的月份和日期部分（2 位數，前面填充零）。同樣，這些 TIME 值通常具有 HH:MM:SS 格式，其中 HH , MM 和 SS 分別表示時間的小時、分鐘和秒。

以下語句演示瞭如何在資料庫表中插入日期值：

INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);

注意： 在 MySQL 中， TIME 值的小時部分可能更大，因為 MySQL 將它們視為已用時間。這意味著 TIME 資料型別不僅可以用於表示一天中的時間（必須小於 24 小時），還可以用於兩個事件之間的時間間隔，可能大於 24 小時，甚至是負數。

跟蹤行建立或修改時間

使用大型應用程式的資料庫時，通常需要在資料庫中儲存記錄建立時間或上次修改時間，例如，儲存使用者註冊時的日期和時間，或使用者上次更新密碼等的時間。

在 MySQL 中，你可以使用 NOW() 函式插入當前時間戳，如下所示：

-- Syntax for MySQL Database 
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());

但是，如果你不想手動插入當前日期和時間，則只需使用 TIMESTAMP 和 DATETIME 資料型別的自動初始化和自動更新屬性即可。

要分配自動屬性，請在列定義中指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句，如下所示：

-- Syntax for MySQL Database 
CREATE TABLE users (
    id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    birth_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

注意： DATETIME 資料型別的自動初始化和更新僅在 MySQL 5.6.5 或更高版本中可用。如果你使用的是舊版本，請使用 TIMESTAMP 替代版本。

提取日期或時間的部分

在某些情況下，你可能只想獲得日期或時間的一部分。在 MySQL 可以使用專門為一個時間值的提取部所設計的功能，例如 YEAR() ， MONTH() ， DAYOFMONTH() ， MONTHNAME() ， DAYNAME() ， HOUR() ， MINUTE() ， SECOND() 等。

以下 SQL 語句將提取 birth_date 列值的 year 部分，例如，如果任何使用者的 birth_date 是 1987-01-14，那麼 YEAR(birth_date) 將返回 1987。

mysql> SELECT name, YEAR(birth_date) FROM users; 

類似地，你可以使用函式 DAYOFMONTH() 來獲取月中的某一天，例如，如果任何使用者的 birth_date 是 1986-10-06，那麼 DAYOFMONTH(birth_date) 將返回 6。

mysql> SELECT name, DAYOFMONTH(birth_date) FROM users; 

格式化日期或時間

如果要在結果集中使用更具描述性和可讀性的日期格式，可以使用 DATE_FORMAT() 和 TIME_FORMAT() 函式重新格式化現有的日期和時間值。

以下 SQL 語句將以更易讀的格式格式化 users 表的 birth_date 列的值，例如從 1987-01-14 轉換成 January 14, 1987。

mysql> SELECT name, DATE_FORMAT(birth_date, '%M %e, %Y') FROM users;