13.8 DATE

在本教程中，我们将向您介绍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表，其生日(birth_date)列使用DATE数据类型。

USE testdb;

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

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

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Max','Su','1992-10-11');

然后，查询people表中的数据，如下所示 -

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

执行上面查询语句，得到以下结果 -

+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Max        | Su        | 1992-10-11 |
+------------+-----------+------------+
1 row in set

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

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Jack','Daniel','01-09-01'),
      ('Lily','Bush','80-09-01');

在第一行，我们使用01(范围在00-69)作为年份，所以MySQL将其转换为2001年。在第二行，我们使用80(范围70-99)作为年份，MySQL将其转换为1980年。

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

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

执行上面查询语句，得到以下结果 -

+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Max        | Su        | 1992-10-11 |
| Jack       | Daniel    | 2001-09-01 |
| Lily       | Bush      | 1980-09-01 |
+------------+-----------+------------+
3 rows in set

MySQL DATE函数

MySQL提供了许多有用的日期功能，可以有效地操作日期。

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

SELECT NOW() as cur_datetime;

执行上面查询语句，得到以下结果 -

mysql> SELECT NOW() as cur_datetime;
+---------------------+
| cur_datetime        |
+---------------------+
| 2017-07-25 21:51:54 |
+---------------------+
1 row in set

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

SELECT DATE(NOW());

执行上面查询语句，得到以下结果 -

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2017-07-25  |
+-------------+
1 row in set

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

SELECT CURDATE();

执行上面查询语句，得到以下结果 -

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2017-07-25 |
+------------+
1 row in set

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

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;

执行上面查询语句，得到以下结果 -

mysql> SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;
+------------+
| today      |
+------------+
| 07/25/2017 |
+------------+
1 row in set

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

SELECT DATEDIFF('2015-11-04','2014-11-04') days;

执行上面查询语句，得到以下结果 -

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

SELECT 
    '2018-01-01' start,
    DATE_ADD('2018-01-01', INTERVAL 1 DAY) 'one day later',
    DATE_ADD('2018-01-01', INTERVAL 1 WEEK) 'one week later',
    DATE_ADD('2018-01-01', INTERVAL 1 MONTH) 'one month later',
    DATE_ADD('2018-01-01', INTERVAL 1 YEAR) 'one year later';

执行上面查询语句，得到以下结果 -

+------------+---------------+----------------+-----------------+----------------+
| start      | one day later | one week later | one month later | one year later |
+------------+---------------+----------------+-----------------+----------------+
| 2018-01-01 | 2018-01-02    | 2018-01-08     | 2018-02-01      | 2019-01-01     |
+------------+---------------+----------------+-----------------+----------------+
1 row in set

类似地，可以使用DATE_SUB函数从日期中减去间隔值：

SELECT 
    '2018-01-01' start,
    DATE_SUB('2018-01-01', INTERVAL 1 DAY) 'one day before',
    DATE_SUB('2018-01-01', INTERVAL 1 WEEK) 'one week before',
    DATE_SUB('2018-01-01', INTERVAL 1 MONTH) 'one month before',
    DATE_SUB('2018-01-01', INTERVAL 1 YEAR) 'one year before';

执行上面查询语句，得到以下结果 -

+------------+----------------+-----------------+------------------+-----------------+
| start      | one day before | one week before | one month before | one year before |
+------------+----------------+-----------------+------------------+-----------------+
| 2018-01-01 | 2017-12-31     | 2017-12-25      | 2017-12-01       | 2017-01-01      |
+------------+----------------+-----------------+------------------+-----------------+
1 row in set

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

SELECT DAY('2018-12-31') day, 
       MONTH('2018-12-31') month, 
       QUARTER('2018-12-31') quarter, 
       YEAR('2018-12-31') year;

执行上面查询语句，得到以下结果 -

mysql> SELECT DAY('2018-12-31') day, 
       MONTH('2018-12-31') month, 
       QUARTER('2018-12-31') quarter, 
       YEAR('2018-12-31') year;
+-----+-------+---------+------+
| day | month | quarter | year |
+-----+-------+---------+------+
|  31 |    12 |       4 | 2018 |
+-----+-------+---------+------+
1 row in set

获得周信息周相关功能。例如，WEEK函数返回周数，WEEKDAY函数返回工作日索引，WEEKOFYEAR函数返回周日历。

SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31') week,
    WEEKOFYEAR('2018-12-31') weekofyear;

执行上面查询语句，得到以下结果 -

mysql> SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31') week,
    WEEKOFYEAR('2018-12-31') weekofyear;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       0 |   52 |          1 |
+---------+------+------------+
1 row in set

如果没有传递第二个参数，或者如果传递参数为0，则week函数将返回带有零的索引的周数。如果传递参数为1，则将返回1索引的周数。

SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31',1) week,
    WEEKOFYEAR('2018-12-31') weekofyear;

执行上面查询语句，得到以下结果 -

mysql> SELECT 
    WEEKDAY('2018-12-31') weekday,
    WEEK('2018-12-31',1) week,
    WEEKOFYEAR('2018-12-31') weekofyear;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       0 |   53 |          1 |
+---------+------+------------+
1 row in set

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