简介：在本教程中，您将学习MySQL DATETIME数据类型以及如何使用一些方便的函数来有效地操纵DATETIME。

MySQL DATETIME数据类型简介

您使用MySQL DATETIME来存储包含日期和时间的值。
当您从DATETIME列查询数据时，MySQL以以下格式显示DATETIME值：

YYYY-MM-DD HH:MM:SS

默认情况下，DATETIME的值范围是1000-01-01 00:00:00到9999-12-31 23:59:59。

DATETIME值使用5个字节存储。
另外，DATETIME值可以包含格式为YYYY-MM-DD HH：MM：SS [.fraction]的尾随小数秒(最高为微秒)，例如2015-12-20 10：01：00.999999。
包括小数秒精度时，DATETIME值需要更多存储，如下表所示：

例如，2015-12-20 10：01：00.999999需要8个字节，2015-12-20 10:01:00需要5个字节，.999999需要3个字节，而2015-12-20 10：01：00.9仅需要6个字节个位元组，以1个位元组为小数秒精度。

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

MySQL DATETIME与TIMESTAMP

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

TIMESTAMP需要4个字节，而DATETIME需要5个字节。
TIMESTAMP和DATETIME都需要额外的字节，以实现小数秒的精度。

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

MySQL将TIMESTAMP存储在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列中的值是不同的。
这是因为当我们更改时区时，TIMESTAMP列将日期和时间值存储在UTC中，因此TIMESTAMP列的值会根据新时区进行调整。

这意味着，如果您使用TIMESTAMP数据存储日期和时间值，则在将数据库移至位于其他时区的服务器时，应认真考虑。

MySQL DATETIME函数

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

SET @dt =  NOW();

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

SELECT @dt;

MySQL DATE功能

要从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 TIME功能

要从DATETIME值中提取时间部分，可以将TIME函数用作以下语句：

SELECT TIME(@dt);

MySQL YEAR，QUARTER，MONTH，WEEK，DAY，HOUR，MINUTE和SECOND函数

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

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

MySQL DATE_FORMAT函数

要格式化DATETIME值，请使用DATE_FORMAT函数。
例如，以下语句根据％H：％i：％s-％W％M％Y格式格式化DATETIME值：

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

MySQL DATE_ADD函数

要将间隔添加到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_SUB函数

要从DATETIME值中减去一个间隔，请使用DATE_SUB函数，如下所示：

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_test表中。

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_test表的每一行中的值进行比较。

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

在本教程中，您学习了MySQL DATETIME数据类型和一些有用的DATETIME函数。