Home » MySQL Basics » A Complete Guide To MySQL DATETIME Data Type

A Complete Guide To MySQL DATETIME Data Type

摘要：在本教程中，您将了解 MySQL DATETIME数据类型以及如何使用一些方便的函数来有效地操作DATETIME 。

MySQL DATETIME数据类型简介

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

YYYY-MM-DD HH:MM:SSCode language: SQL (Structured Query Language) (sql)

默认情况下， 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值需要更多存储空间，如下表所示：

小数秒精度存储（字节）
0 0
1, 2 1
3, 4 2
5, 6 3

例如， 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 日期时间与时间戳

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 以 UTC 值存储TIMESTAMP 。但是，MySQL 按原样存储没有时区的DATETIME值。让我们看下面的例子。

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

SET time_zone = '+00:00';Code language: SQL (Structured Query Language) (sql)

接下来，使用以下语句创建一个名为timestamp_n_datetime的表，该表由两列组成： ts和dt ，类型为TIMESTAMP和DATETIME 。

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);Code language: SQL (Structured Query Language) (sql)

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

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());Code language: SQL (Structured Query Language) (sql)

之后，从timestamp_n_datetime表中查询数据。

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME vs TIMESTAMP

DATETIME和TIMESTAMP列中的值相同。

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

SET time_zone = '+03:00';

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME vs TIMESTAMP timezone changes

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

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

MySQL 日期时间函数

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

SET @dt =  NOW();Code language: SQL (Structured Query Language) (sql)

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

SELECT @dt;Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - NOW 函数

MySQL 日期函数

要从DATETIME值中提取日期部分，请使用DATE函数，如下所示：

SELECT DATE(@dt);Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - DATE 函数示例

如果您想根据日期查询数据，但列中存储的数据同时基于日期和时间，则此功能非常有用。

让我们看下面的例子。

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');Code language: SQL (Structured Query Language) (sql)

假设您想知道哪一行是在2015-11-05创建的，您可以使用以下查询：

SELECT 
    *
FROM
    test_dt
WHERE
    created_at = '2015-11-05';Code language: SQL (Structured Query Language) (sql)

它不返回任何行。

这是因为created_at列不仅包含日期还包含时间。要更正它，请使用DATE函数，如下所示：

SELECT 
    *
FROM
    test_dt
WHERE
    DATE(created_at) = '2015-11-05';Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - 日期函数

它按预期返回一行。如果表中有很多行，MySQL 必须执行全表扫描来查找符合条件的行。

MySQL 时间函数

要从DATETIME值中提取时间部分，请使用TIME函数，如下语句：

SELECT TIME(@dt);Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - 时间函数

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);Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - datetime functions

MySQL DATE_FORMAT 函数

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

SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - DATE_FORMAT function

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';Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - DATE_ADD 函数

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';Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - DATE_SUB 函数

MySQL DATE_DIFF 函数

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

请参阅以下示例。

首先，创建一个名为datediff_test的表，其中有一列数据类型为DATETIME 。

CREATE TABLE datediff_test (
    dt DATETIME
);Code language: SQL (Structured Query Language) (sql)

其次，将一些行插入到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');Code language: SQL (Structured Query Language) (sql)

第三，使用DATEDIFF函数将当前日期和时间与datediff_test表中每行的值进行比较。

SELECT 
    dt, 
    DATEDIFF(NOW(), dt)
FROM
    datediff_test;Code language: SQL (Structured Query Language) (sql)
MySQL DATETIME - DATEDIFF Example

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

本教程有帮助吗？
MySQL CHAR Data Type
Previously
MySQL CHAR Data Type
Up Next
MySQL DECIMAL Data Type
MySQL DECIMAL Data Type