SQL 日期和时间

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

日期和时间处理

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

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

类型预设格式容许值
DATEYYYY-MM-DD1000-01-01 - 9999-12-31
TIMEHH:MM:SS or HHH:MM:SS-838:59:59 - 838:59:59
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 - 9999-12-31 23:59:59
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:00 - 2037-12-31 23:59:59
YEARYYYY1901 - 2155

DATE值的格式为YYYY-MM-DD,其中YYYY表示全年(4位数字),而MM和DD分别表示日期的月和日部分(两个前零的数字)。时间值通常采用HH:MM:SS格式,其中HH、MM、SS分别表示时间的小时、分钟、秒部分。

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

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

注意:在MySQL中,小时部分的时间值可能更大,因为MySQL将它们视为运行时间。这意味着时间数据类型不仅可以用于表示一天中的一个时间(必须小于24小时),还可以用于表示两个事件之间的时间间隔,可能大于24小时,甚至为负值。

跟踪行创建或修改时间

在使用大型应用程序的数据库时,通常需要在数据库中存储记录创建时间或上次修改时间,例如,存储用户注册时或用户上次更新密码时的日期和时间等。

在MySQL中,您可以使用NOW()函数插入当前时间戳,如下所示:

-- MySQL数据库的语法
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());

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

要分配自动属性,请在列定义中指定DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句,如下所示:

-- MySQL数据库的语法
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列值的年份部分,例如 如果任何用户的生日是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;