目录

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

MySQL中有五种数据类型用于管理日期和时间，包括：DATE，TIME，DATETIME，TIMESTAMP和YEAR。本文中主要探讨DATE数据类型。MySQLDATE使用yyyy-mm-dd格式来存储日期值。如果您想以其他的日期格式显示，比如mm-dd-yyyy，您可以使用DATE_FORMAT

MySQL 中有五种数据类型用于管理日期和时间，包括：DATE， TIME，DATETIME， TIMESTAMP 和 YEAR。本文中主要探讨 DATE 数据类型。

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

MySQL DATE 类型值的范围从 1000-01-01 到 9999-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 将被转换为 2009，99 将被转换为 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(): 获取当前日期和时间
  • CURDATE(): 获取当前日期
  • DATE(): 获取日期部分
  • DATE_FORMAT(): 格式化输出日期
  • DATEDIFF(): 计算两个日期之间的天数
  • DATE_ADD(): 在给定日期上增加给定的时间间隔
  • DATE_SUB(): 在给定日期上减少给定的时间间隔
  • DAY(): 返回日期中天
  • MONTH(): 返回月份
  • QUARTER(): 返回季节
  • YEAR(): 返回年份
  • WEEK(): 函数返回给定日期是一年周的第几周
  • WEEKDAY(): 函数返回工作日索引
  • WEEKOFYEAR(): 函数返回日历周

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 格式插入日期值。