Skip to main content

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

MySQL日期数据类型简介

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

例如,您可能更喜欢使用mm-dd-yyyy格式,但您不能。相反,您可以遵循标准的日期格式,并使用date_format函数以您想要的方式格式化日期。

MySQL使用3个字节来存储日期值。日期值的范围从1000-01-01到9999-12-31。如果要存储超出此范围的日期值,则需要使用非时态数据类型,如integer,例如三列,以及年、月和日的每列。您还需要创建存储函数来模拟MySQL提供的内置日期函数,这是不推荐的。

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

两位数年份的MySQL日期值

MySQL使用四位数字存储日期值的年份。如果使用两位数的年份值,MySQL仍然按照以下规则接受它们:

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

让我们看看下面的例子。

首先,创建一个名为people with birth date column的表,其数据类型为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
);

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

INSERT INTO people(first_name,last_name,birth_date)
VALUES('John','Doe','1990-09-01');

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

SELECT 
first_name,
last_name,
birth_date
FROM
people;

MySQL DATE Data Type Example

之后,使用两位数的年份格式将数据插入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;

MySQL Date Data Type Two-digit Year Example

MySQL日期函数

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

要获取当前日期和时间,可以使用NOW()函数。

SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2017-05-13 07:59:38 |
+---------------------+
1 row in set (0.02 sec)

要仅获取DATETIME值的日期部分,可以使用date()函数。

SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2015-07-13 |
+-------------+
1 row in set (0.01 sec)

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

SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2015-07-13 |
+------------+
1 row in set (0.02 sec)

要设置日期值的格式,可以使用date_format函数。以下语句使用日期格式模式%m/%d/%Y对asmm/dd/yyyy日期进行格式设置:

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;
+------------+
| today |
+------------+
| 07/13/2015 |
+------------+
1 row in set (0.02 sec)

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

SELECT DATEDIFF('2015-11-04','2014-11-04') days;
+------+
| days |
+------+
| 365 |
+------+
1 row in set (0.02 sec)

要向日期值添加天数、周数、月数、年数等,请使用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';

MySQL DATE - DATE_ADD example

类似地,可以使用date_子函数从日期中减去间隔:

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

MySQL DATE - DATE_SUB example

如果要获取日期值的日、月、季和年,可以使用相应的函数日、月、季和年,如下所示:

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

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

SELECT 
WEEKDAY('2000-12-31') weekday,
WEEK('2000-12-31') week,
WEEKOFYEAR('2000-12-31') weekofyear;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
| 6 | 53 | 52 |
+---------+------+------------+
1 row in set (0.04 sec)

如果不传递第二个参数或传递0,week函数将返回带零基索引的周数。如果你通过了1,它将返回一周的数字和1索引。

SELECT 
WEEKDAY('2000-12-31') weekday,
WEEK('2000-12-31',1) week,
WEEKOFYEAR('2000-12-31') weekofyear;
+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
| 6 | 52 | 52 |
+---------+------+------------+
1 row in set (0.00 sec)

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