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;
DATETIME和TIMESTAMP列中的值都相同。
最后，将连接的时区设置为+03:00，并再次从timestamp_n_datetime表中查询数据。
SET time_zone = '+03:00';
SELECT
ts,
dt
FROM
timestamp_n_datetime;
如您所见，TIMESTAMP列中的值是不同的。这是因为时间戳列以UTC格式存储日期和时间值。当我们更改时区时，时间戳列的值会根据新时区进行调整。
这意味着，如果使用时间戳数据存储日期和时间值，那么在将数据库移动到位于不同时区的服务器时，应该认真考虑。
MySQL日期时间函数
下面的语句使用NOW（）函数将变量@dt设置为当前日期和时间。
SET @dt = NOW();
要查询@dt变量的值，请使用以下SELECT语句：
SELECT @dt;
MySQL日期函数
要从DATETIME值中提取日期部分，请按如下方式使用date函数：
SELECT DATE(@dt);
如果您希望根据日期查询数据，但列中存储的数据同时基于日期和时间，则此函数非常有用。
让我们看看下面的例子。
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必须执行完整的表扫描，以找到符合条件的行。
MySQL时间函数
要从DATETIME值中提取时间部分，请将time函数用作以下语句：
SELECT TIME(@dt);
MySQL年、季、月、周、日、时、分、秒功能
要从DATETIME值中获取年、季度、月、周、日、小时、分钟和秒，请使用以下语句中所示的函数：
SELECT
HOUR(@dt),
MINUTE(@dt),
SECOND(@dt),
DAY(@dt),
WEEK(@dt),
MONTH(@dt),
QUARTER(@dt),
YEAR(@dt);
MySQL DATE_格式函数
要设置日期时间值的格式，可以使用DATE_format函数。例如，以下语句基于%H格式化日期时间值：%i:%s-%W%M%Y格式：
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');
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 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 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日期时间数据类型和一些有用的日期时间函数。