SQL 日期和时间

在本教程中,你将学习如何在 SQL 中使用日期和时间。

日期和时间操纵

除了字符串和数字之外,你还经常需要在数据库中存储日期和/或时间值,例如用户的出生日期,员工的招聘日期,未来事件的日期,表中特定行的创建或修改日期和时间,等等。

这种类型的数据称为时态数据,每个数据库引擎都有默认的存储格式和用于存储它们的数据类型。下表显示了 MySQL 数据库服务器支持的用于处理日期和时间的数据类型。

类型 默认格式 允许值
DATE YYYY-MM-DD 1000-01-019999-12-31
TIME HH:MM:SS or HHH:MM:SS -838:59:59838:59:59
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:009999-12-31 23:59:59
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:002037-12-31 23:59:59
YEAR YYYY 19012155

DATE 值具有 YYYY-MM-DD 格式,其中 YYYY 表示全年(4 位数),而 MMDD 表示日期的月份和日期部分(2 位数,前面填充零)。同样,这些 TIME 值通常具有 HH:MM:SS 格式,其中 HH , MMSS 分别表示时间的小时、分钟和秒。

以下语句演示了如何在数据库表中插入日期值:

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());

但是,如果你不想手动插入当前日期和时间,则只需使用 TIMESTAMPDATETIME 数据类型的自动初始化和自动更新属性即可。

要分配自动属性,请在列定义中指定 DEFAULT CURRENT_TIMESTAMPON 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_date1987-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;