MySQL 日期和日期函数终极指南

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

MySQL DATE数据类型简介

MySQL DATE是用于管理日期值的五种时态数据类型之一。 MySQL 使用yyyy-mm-dd格式来存储日期值。此格式是固定的，无法更改。

例如，您可能更喜欢使用mm-dd-yyyy格式，但实际上不能。相反，您可以遵循标准日期格式并使用DATE_FORMAT函数按照您想要的方式设置日期格式。

MySQL 使用 3 个字节来存储DATE值。 DATE值范围从1000-01-01到9999-12-31 。如果要存储超出此范围的日期值，则需要使用非时间数据类型（例如整数），例如三列，每列代表年、月和日。还需要创建存储函数来模拟MySQL提供的内置日期函数，不建议这样做。

当禁用严格模式时，MySQL 会将任何无效日期（例如2015-02-30转换为零日期值0000-00-00 。

MySQL 具有两位数年份的日期值

MySQL 使用四位数字存储日期值的年份。如果您使用两位数年份值，MySQL 仍然接受它们并遵循以下规则：

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

但是，两位数的日期值不明确，因此应避免使用它。

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

首先，创建一个名为 people 的表，其中出生日期列的数据类型为DATE 。

CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

接下来，在people表中插入一行。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('John','Doe','1990-09-01');Code language: SQL (Structured Query Language) (sql)

然后，从people表中查询数据。

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;Code language: SQL (Structured Query Language) (sql)

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

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Jack','Daniel','01-09-01'),
      ('Lily','Bush','80-09-01');Code language: SQL (Structured Query Language) (sql)

在第一行中，我们使用 01（范围 00-69）作为年份，因此 MySQL 将其转换为 2001。在第二行中，我们使用 80（范围 70-99）作为年份，MySQL 将其转换为 1980。

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

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;Code language: SQL (Structured Query Language) (sql)

MySQL Date Data Type Two-digit Year Example

MySQL `DATE`函数

MySQL 提供了许多有用的日期函数，使您可以有效地操作日期。

要获取当前日期和时间，请使用NOW()函数。

SELECT NOW();Code language: SQL (Structured Query Language) (sql)

+---------------------+
| NOW()               |
+---------------------+
| 2017-05-13 07:59:38 |
+---------------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

要仅获取DATETIME值的日期部分，请使用DATE()函数。

SELECT DATE(NOW());Code language: SQL (Structured Query Language) (sql)

+-------------+
| DATE(NOW()) |
+-------------+
| 2015-07-13  |
+-------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

要获取当前系统日期，请使用CURDATE()函数，如下所示：

SELECT CURDATE();Code language: SQL (Structured Query Language) (sql)

+------------+
| CURDATE()  |
+------------+
| 2015-07-13 |
+------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

要格式化日期值，请使用DATE_FORMAT函数。以下语句使用日期格式模式%m/%d/%Y日期格式化为mm/dd/yyyy ：

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;Code language: SQL (Structured Query Language) (sql)

+------------+
| today      |
+------------+
| 07/13/2015 |
+------------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

要计算两个日期值之间的天数，请使用DATEDIFF函数，如下所示：

SELECT DATEDIFF('2015-11-04','2014-11-04') days;Code language: SQL (Structured Query Language) (sql)

+------+
| days |
+------+
|  365 |
+------+
1 row in set (0.02 sec)Code language: SQL (Structured Query Language) (sql)

要将天数、周数、月数、年数等添加到日期值，可以使用DATE_ADD函数：

SELECT 
    '2015-01-01' start,
    DATE_ADD('2015-01-01', INTERVAL 1 DAY) 'one day later',
    DATE_ADD('2015-01-01', INTERVAL 1 WEEK) 'one week later',
    DATE_ADD('2015-01-01', INTERVAL 1 MONTH) 'one month later',
    DATE_ADD('2015-01-01', INTERVAL 1 YEAR) 'one year later';Code language: SQL (Structured Query Language) (sql)

同样，您可以使用DATE_SUB函数从日期中减去间隔：

SELECT 
    '2015-01-01' start,
    DATE_SUB('2015-01-01', INTERVAL 1 DAY) 'one day before',
    DATE_SUB('2015-01-01', INTERVAL 1 WEEK) 'one week before',
    DATE_SUB('2015-01-01', INTERVAL 1 MONTH) 'one month before',
    DATE_SUB('2015-01-01', INTERVAL 1 YEAR) 'one year before';Code language: SQL (Structured Query Language) (sql)

如果要获取日期值的日、月、季度、年，可以使用相应的函数DAY 、 MONTH 、 QUARTER和YEAR ，如下所示：

SELECT DAY('2000-12-31') day, 
       MONTH('2000-12-31') month, 
       QUARTER('2000-12-31') quarter, 
       YEAR('2000-12-31') year;Code language: SQL (Structured Query Language) (sql)

+------+-------+---------+------+
| day  | month | quarter | year |
+------+-------+---------+------+
|   31 |    12 |       4 | 2000 |
+------+-------+---------+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

获取星期信息星期相关函数。例如， WEEK函数返回周数， WEEKDAY函数返回工作日索引， WEEKOFYEAR函数返回日历周。

SELECT 
    WEEKDAY('2000-12-31') weekday,
    WEEK('2000-12-31') week,
    WEEKOFYEAR('2000-12-31') weekofyear;Code language: SQL (Structured Query Language) (sql)

+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       6 |   53 |         52 |
+---------+------+------------+
1 row in set (0.04 sec)Code language: SQL (Structured Query Language) (sql)

如果不传递第二个参数或者传递 0，week 函数将返回索引从 0 开始的周数。如果传递 1，它将返回索引从 1 开始的周数。

SELECT 
    WEEKDAY('2000-12-31') weekday,
    WEEK('2000-12-31',1) week,
    WEEKOFYEAR('2000-12-31') weekofyear;Code language: SQL (Structured Query Language) (sql)

+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       6 |   52 |         52 |
+---------+------+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

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

本教程有帮助吗？

MySQL DATE数据类型简介

MySQL 具有两位数年份的日期值

MySQL DATE函数

MySQL `DATE`函数