SQL 日期与时间

本章将学习如何使用SQL处理日期和时间。

日期和时间操作

除了字符串和数字，通常还需要在数据库中存储日期和时间，例如：用户的出生日期、员工的雇佣日期等等。

这种类型的数据称为时态数据，每个数据库引擎都有存储它们的默认存储格式和数据类型。下表显示了MySQL中日期和时间相关的数据类型。

• 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;