MySQL 日期类型及相关函数介绍

在本文中,我们将向您介绍 MySQL DATE 数据类型,并向您展示一些有用的日期函数来有效地处理日期数据。

MySQL 中有五种数据类型用于管理日期和时间,包括:DATETIMEDATETIMETIMESTAMPYEAR。本文中主要探讨 DATE 数据类型。

MySQL DATE 使用 yyyy-mm-dd 格式来存储日期值。如果您想以其他的日期格式显示,比如 mm-dd-yyyy,您可以使用 DATE_FORMAT 函数将日期格式化为您需要的格式。

MySQL DATE 类型值的范围从 1000-01-019999-12-31

当您向 DATE 列中插入值时,您可以使用 yyyy-mm-dd 或者 yy-mm-dd 格式。

在严格模式下,您不能插入无效日期,比如:2018-08-32。否则 MySQL 会给出错误。在非严格模式下,MySQL 会将无效日期转为 0000-00-00

MySQL DATE 语法

以下是创建 DATE 列的语法:

column_name DATE;

这里, 我们无需为 DATE 指定任何属性。

MySQL 日期实例

上面我们说过,您可以将 yyyy-mm-dd 或者 yy-mm-dd 格式的日期值插入到 DATE 列中。

如果您使用两位数的年份值,MySQL 仍然按照以下规则将它转为四位数的年份值:

  • 00-69 范围内的年份值将转换为 2000-2069
  • 70-99 范围内的年份值将转换为 1970-1999

虽然规则时明确的,但是,具有两位数的日期值会给读取您代码的人带来困惑,因此应该避免使用两位数的年份。

让我们看一下下面的例子。

首先,创建一个表 test_date, 它包含 DATE 数据类型的列 created_date

CREATE TABLE test_date (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_date DATE NOT NULL
);

接下来,在 test_date 表中插入一行

INSERT INTO test_date(created_date)
VALUES ('2008-08-08');

然后,从 test_date 表中查询数据)。

SELECT * FROM test_date;
+----+--------------+
| id | created_date |
+----+--------------+
|  1 | 2008-08-08   |
+----+--------------+

之后,使用两位数的年份格式将数据插入 people 表中。

INSERT INTO test_date(created_date)
VALUES ('09-09-09'),  ('99-09-09');

这里,根据转换规则,年份 09 将被转换为 200999 将被转换为 1999

最后,我们可以从 test_date 表中查询数据,以检查数据是否根据转换规则进行了转换。

SELECT * FROM test_date;
+----+--------------+
| id | created_date |
+----+--------------+
|  1 | 2008-08-08   |
|  2 | 2009-09-09   |
|  3 | 1999-09-09   |
+----+--------------+

让我们再尝试一个插入错误日期的语句:

INSERT INTO test_date(created_date)
VALUES ('2008-02-30');

这里 MySQL 会返回错误: ERROR 1292 (22007): Incorrect date value: ‘2008-02-30’ for column ‘created_date’ at row 1。

MySQL DATE 函数

MySQL 提供了许多有用的日期函数,允许您有效地操作日期。以下列出了常用的日期函数:

NOW()

NOW() 函数用来获取当前日期和时间。请看下面的语句:

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-03-25 06:47:58 |
+---------------------+

CURDATE()

CURDATE() 用来获取获取当前系统日期。请看下面的语句:

SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2022-03-25 |
+------------+

DATE()

DATE() 函数用来返回一个日期或者日期时间值的日期部分。请看下面的语句:

SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2022-03-25  |
+-------------+

DATE_FORMAT()

要格式化日期值,请使用 DATE_FORMAT() 函数。以下语句使用日期格式模式 %m/%d/%Y 格式化当前日期:

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;
+------------+
| today      |
+------------+
| 03/25/2022 |
+------------+

DATEDIFF()

要计算两个日期值之间的天数,请 DATEDIFF() 按如下方式使用该函数:

SELECT DATEDIFF('2021-01-01','2022-01-01') days;
+------+
| days |
+------+
| -365 |
+------+

这里,如果第一个参数早于第二个参数返回为负数,否则返回为正数或者 0。

DATE_ADD()

DATE_ADD() 函数用于将天数、周数、月数、年数等添加到给定的日期值:

SELECT CURDATE() `今天`,
    DATE_ADD(CURDATE(), INTERVAL 1 DAY) '一天后',
    DATE_ADD(CURDATE(), INTERVAL 1 WEEK) '一周后',
    DATE_ADD(CURDATE(), INTERVAL 1 MONTH) '一月后',
    DATE_ADD(CURDATE(), INTERVAL 1 YEAR) '一年后';
+------------+------------+------------+------------+------------+
| 今天       | 一天后     | 一周后     | 一月后     | 一年后     |
+------------+------------+------------+------------+------------+
| 2022-03-25 | 2022-03-26 | 2022-04-01 | 2022-04-25 | 2023-03-25 |
+------------+------------+------------+------------+------------+

DATE_SUB()

同样,DATE_SUB() 被用于从日期中减去一个时间间隔

SELECT CURDATE() `今天`,
    DATE_SUB(CURDATE(), INTERVAL 1 DAY) '一天前',
    DATE_SUB(CURDATE(), INTERVAL 1 WEEK) '一周前',
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) '一月前',
    DATE_SUB(CURDATE(), INTERVAL 1 YEAR) '一年前';
+------------+------------+------------+------------+------------+
| 今天       | 一天前     | 一周前     | 一月前     | 一年前     |
+------------+------------+------------+------------+------------+
| 2022-03-25 | 2022-03-24 | 2022-03-18 | 2022-02-25 | 2021-03-25 |
+------------+------------+------------+------------+------------+

DAY, MONTH, QUARTER, YEAR

如果你想获得的日,月,季度和日期值的年份,您可以使用相应 DAY()MONTH()QUARTER(),和 YEAR() 函数 如下:

SELECT DAY(CURDATE()) `day`,
    MONTH(CURDATE()) `month`,
    QUARTER(CURDATE()) `quarter`,
    YEAR(CURDATE()) `year`;
+------+-------+---------+------+
| day  | month | quarter | year |
+------+-------+---------+------+
|   25 |     3 |       1 | 2022 |
+------+-------+---------+------+

WEEK, WEEKDAY, WEEKOFYEAR

WEEK 函数返回周数, WEEKDAY 函数返回工作日索引, WEEKOFYEAR 函数返回日历周。

SELECT WEEKDAY(CURDATE()) `weekday`,
    WEEK(CURDATE()) `week`,
    WEEKOFYEAR(CURDATE()) `weekofyear`;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       4 |   12 |         12 |
+---------+------+------------+

结论

在本教程中,您学习了 MySQL DATE 数据类型以及如何使用一些有用的日期函数来操作日期值。

  • MySQL DATE 数据类型的存储格式为 yyyy-mm-dd
  • 您可以使用 yyyy-mm-dd 或者 yy-mm-dd 格式插入日期值。