本章将学习如何使用SQL处理日期和时间。
日期和时间操作
除了字符串和数字,通常还需要在数据库中存储日期和时间,例如:用户的出生日期、员工的雇佣日期等等。
这种类型的数据称为时态数据,每个数据库引擎都有存储它们的默认存储格式和数据类型。下表显示了MySQL中日期和时间相关的数据类型。
类型 | 默认格式 | 取值范围 |
---|---|---|
DATE |
YYYY-MM-DD |
1000-01-01 to 9999-12-31 |
TIME |
HH:MM:SS or HHH:MM:SS |
-838:59:59 to 838:59:59 |
DATETIME |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 to 9999-12-31 23:59:59 |
TIMESTAMP |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:00 to 2037-12-31 23:59:59 |
YEAR |
YYYY |
1901 to 2155 |
DATE
值采用YYYY-MM-DD
格式,其中YYYY
表示年份(4位),MM
和DD
分别表示月份和日期部分(2位以0开头)。TIME
值通常采用HH:MM:SS
格式,其中HH
、MM
和SS
分别表示时、分、秒。
下面的语句演示了如何在表中插入日期值:
INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);
跟踪行创建时间与修改时间
在处理大型应用程序的数据库时,经常需要存储某一行的创建时间或最后修改时间,例如,存储用户注册时间,或用户最后访问时间,等等。
在MySQL中,可以使用NOW()
函数插入当前时间戳,如下所示:
-- Syntax for MySQL Database
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());
但是,如果不希望手动插入当前时间,则可以简单使用TIMESTAMP
和DATETIME
数据类型的自动初始化和自动更新属性。
要自动生成时间戳,在相关字段定义中设置默认值为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
列值的年份部分,例如,用户的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-1-14
变为1987年1月14日
。
mysql> SELECT name, DATE_FORMAT(birth_date, '%M %e, %Y') FROM users;