SQL 日期与时间

本章将学习如何使用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位),MMDD分别表示月份和日期部分(2位以0开头)。
  • TIME值通常采用HH:MM:SS格式,其中HHMMSS分别表示时、分、秒。

下面的语句演示了如何在表中插入日期值:

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

但是,如果不希望手动插入当前时间,则可以简单使用TIMESTAMPDATETIME数据类型的自动初始化和自动更新属性。

要自动生成时间戳,在相关字段定义中设置默认值为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列值的年份部分,例如,用户的birth_date1987-01-14,则YEAR(birth_date)将返回1987。

mysql> SELECT name, YEAR(birth_date) FROM users;

类似地,您可以使用函数DAYOFMONTH()来获取日期,例如,用户的birth_date1986-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;


浙ICP备17015664号 浙公网安备 33011002012336号 联系我们 网站地图  
@2019 qikegu.com 版权所有,禁止转载