Skip to main content

MySQL日期时间数据类型完整指南

MySQL日期时间数据类型简介

使用MySQL DATETIME存储一个同时包含日期和时间的值。当您从DATETIME列查询数据时,MySQL会以以下格式显示DATETIME值:

YYYY-MM-DD HH:MM:SS

默认情况下,日期时间值的范围为1000-01-01 00:00:00到9999-12-31 23:59:59。

DATETIME值使用5个字节进行存储。此外,日期时间值可以包括格式为YYYY-MM-DD HH:MM:SS[.分数]的最长微秒的尾随分数,例如2015-12-20 10:01:00.99999。包含小数秒精度时,日期时间值需要更多存储空间,如下表所示:

例如,2015-12-20 10:01:00.999999需要8个字节,2015-12-20 10:01:00需要5个字节,而2015-12-20 10:01:00.9只需要6个字节,小数秒精度需要1个字节。

请注意,在MySQL 5.6.4之前,DATETIME值需要8字节的存储空间,而不是5字节。

MySQL日期时间与时间戳

MySQL提供了另一种与DATETIME类似的时态数据类型,称为TIMESTAMP。

时间戳需要4个字节,而日期时间需要5个字节。TIMESTAMP和DATETIME都需要额外的字节来实现小数秒精度。

时间戳值的范围从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC。如果要存储超过2038的时态值,应该使用DATETIME而不是TIMESTAMP。

MySQL以UTC值存储时间戳。然而,MySQL存储DATETIME值时没有时区。让我们看看下面的例子。

首先,将当前连接的时区设置为+00:00。

SET time_zone = '+00:00';

接下来,使用以下语句创建一个名为timestamp_n_datetime的表,该表由两列组成:ts和dt,其中包含timestamp和datetime类型。

CREATE TABLE timestamp_n_datetime (
id INT AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP,
dt DATETIME
);

然后,将当前日期和时间插入timestamp_n_datetime表的ts和dt列,

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());

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

SELECT 
ts,
dt
FROM
timestamp_n_datetime;

MySQL DATETIME vs TIMESTAMP

DATETIME和TIMESTAMP列中的值都相同。

最后,将连接的时区设置为+03:00,并再次从timestamp_n_datetime表中查询数据。

SET time_zone = '+03:00';

SELECT
ts,
dt
FROM
timestamp_n_datetime;

MySQL DATETIME vs TIMESTAMP timezone changes

如您所见,TIMESTAMP列中的值是不同的。这是因为时间戳列以UTC格式存储日期和时间值。当我们更改时区时,时间戳列的值会根据新时区进行调整。

这意味着,如果使用时间戳数据存储日期和时间值,那么在将数据库移动到位于不同时区的服务器时,应该认真考虑。

MySQL日期时间函数

下面的语句使用NOW()函数将变量@dt设置为当前日期和时间。

SET @dt =  NOW();

要查询@dt变量的值,请使用以下SELECT语句:

SELECT @dt;

MySQL DATETIME - NOW function

MySQL日期函数

要从DATETIME值中提取日期部分,请按如下方式使用date函数:

SELECT DATE(@dt);

MySQL DATETIME - DATE function example

如果您希望根据日期查询数据,但列中存储的数据同时基于日期和时间,则此函数非常有用。

让我们看看下面的例子。

CREATE TABLE test_dt (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME
);

INSERT INTO test_dt(created_at)
VALUES('2015-11-05 14:29:36');

假设您想知道2015-11-05创建了哪一行,请使用以下查询:

SELECT 
*
FROM
test_dt
WHERE
created_at = '2015-11-05';

它不返回任何行。

这是因为created_at列不仅包含日期,还包含时间。要更正它,请按如下方式使用DATE函数:

SELECT 
*
FROM
test_dt
WHERE
DATE(created_at) = '2015-11-05';

MySQL DATETIME - DATE function

它按预期返回一行。如果表中有很多行,MySQL必须执行完整的表扫描,以找到符合条件的行。

MySQL时间函数

要从DATETIME值中提取时间部分,请将time函数用作以下语句:

SELECT TIME(@dt);

MySQL DATETIME - TIME function

MySQL年、季、月、周、日、时、分、秒功能

要从DATETIME值中获取年、季度、月、周、日、小时、分钟和秒,请使用以下语句中所示的函数:

SELECT 
HOUR(@dt),
MINUTE(@dt),
SECOND(@dt),
DAY(@dt),
WEEK(@dt),
MONTH(@dt),
QUARTER(@dt),
YEAR(@dt);

MySQL DATETIME - datetime functions

MySQL DATE_格式函数

要设置日期时间值的格式,可以使用DATE_format函数。例如,以下语句基于%H格式化日期时间值:%i:%s-%W%M%Y格式:

SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');

MySQL DATETIME - DATE_FORMAT function

MySQL日期添加函数

要向DATETIME值添加间隔,请使用DATE_add函数,如下所示:

SELECT @dt start, 
DATE_ADD(@dt, INTERVAL 1 SECOND) '1 second later',
DATE_ADD(@dt, INTERVAL 1 MINUTE) '1 minute later',
DATE_ADD(@dt, INTERVAL 1 HOUR) '1 hour later',
DATE_ADD(@dt, INTERVAL 1 DAY) '1 day later',
DATE_ADD(@dt, INTERVAL 1 WEEK) '1 week later',
DATE_ADD(@dt, INTERVAL 1 MONTH) '1 month later',
DATE_ADD(@dt, INTERVAL 1 YEAR) '1 year later';

MySQL DATETIME - DATE_ADD function

MySQL DATE_子函数

要从DATETIME值中减去间隔,可以使用DATE_子函数,如下所示:

SELECT @dt start, 
DATE_SUB(@dt, INTERVAL 1 SECOND) '1 second before',
DATE_SUB(@dt, INTERVAL 1 MINUTE) '1 minute before',
DATE_SUB(@dt, INTERVAL 1 HOUR) '1 hour before',
DATE_SUB(@dt, INTERVAL 1 DAY) '1 day before',
DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before',
DATE_SUB(@dt, INTERVAL 1 MONTH) '1 month before',
DATE_SUB(@dt, INTERVAL 1 YEAR) '1 year before';

MySQL DATETIME - DATE_SUB function

MySQL DATE_DIFF函数

要计算两个DATETIME值之间的天数差,可以使用DATEDIFF函数。请注意,DATEDIFF函数在计算中只考虑DATETIME值的日期部分。

请参见下面的示例。

首先,创建一个名为datediff_test的表,该表有一个数据类型为DATETIME的列。

CREATE TABLE datediff_test (
dt DATETIME
);

其次,在datediff_测试表中插入一些行。

INSERT INTO datediff_test(dt)
VALUES('2010-04-30 07:27:39'),
('2010-05-17 22:52:21'),
('2010-05-18 01:19:10'),
('2010-05-22 14:17:16'),
('2010-05-26 03:26:56'),
('2010-06-10 04:44:38'),
('2010-06-13 13:55:53');

第三,使用DATEDIFF函数将当前日期和时间与DATEDIFF_测试表中每一行的值进行比较。

SELECT 
dt,
DATEDIFF(NOW(), dt)
FROM
datediff_test;

MySQL DATETIME - DATEDIFF Example

在本教程中,您了解了MySQL日期时间数据类型和一些有用的日期时间函数。