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;