MySQL datetime类型

您所在的位置：网站首页 › 数据表日期格式设置方法是什么 › MySQL datetime类型

MySQL datetime类型

2024-07-16 04:42:30| 来源: 网络整理| 查看: 265

在本教程中，您将了解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]例如：2017-12-20 10:01:00.999999的尾数有小数秒。当包含小数秒精度时，DATETIME值需要更多存储，如下表所示：

分数秒精度存储(字节) 0 0 1，2 1 3，4 2 5，6 3

例如，2017-12-20 10:01:00.999999需要8个字节，2015-12-20 10:01:00需要5个字节，3个字节为.999999，而2017-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。

接下来，创建一个名为timestamp_n_datetime的表，它由两列组成：ts和dt，这两列分别使用TIMESTAMP和DATETIME类型，如以下语句 -

USE testdb; 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;

执行上面查询语句，得到以下结果 -

+---------------------+---------------------+ | ts | dt | +---------------------+---------------------+ | 2017-07-26 00:38:14 | 2017-07-26 00:38:14 | +---------------------+---------------------+ 1 row in set

DATETIME和TIMESTAMP列中的两个值相同。

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

SET time_zone = '+03:00'; SELECT ts, dt FROM timestamp_n_datetime;

执行上面查询语句，得到以下结果 -

+---------------------+---------------------+ | ts | dt | +---------------------+---------------------+ | 2017-07-25 19:38:14 | 2017-07-26 00:38:14 | +---------------------+---------------------+ 1 row in set

可以看到，ts列为TIMESTAMP数据类型的值变了。这是因为在更改时区时，TIMESTAMP列以UTC为单位存储日期和时间值，根据新时区调整TIMESTAMP列的值。

这意味着如果使用TIMESTAMP数据来存储日期和时间值，则在将数据库移动到位于不同时区的服务器时时间的值可能不一样，所以应该认真考虑这个问题。

MySQL DATETIME函数

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

SET @dt = NOW();

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

SELECT @dt;

执行上面查询语句，得到以下结果 -

mysql> SELECT @dt; +---------------------+ | @dt | +---------------------+ | 2017-07-25 19:41:14 | +---------------------+ 1 row in set

MySQL DATE函数

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

mysql> SELECT DATE(@dt); +------------+ | DATE(@dt) | +------------+ | 2017-07-25 | +------------+ 1 row in set

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

下面来看看看下面的例子。

USE testdb; CREATE TABLE test_dt ( id INT AUTO_INCREMENT PRIMARY KEY, created_at DATETIME ); INSERT INTO test_dt(created_at) VALUES('2017-11-05 20:29:36');

假设您想知道在2017-11-05当天创建的行，请使用以下查询：

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

执行上面查询语句，得到以下结果 -

mysql> SELECT * FROM test_dt WHERE created_at = '2017-11-05'; Empty set

它不返回任何行记录。

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

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

执行上面查询语句，得到以下结果 -

+----+---------------------+ | id | created_at | +----+---------------------+ | 1 | 2017-11-05 20:29:36 | +----+---------------------+ 1 row in set

它按预期返回一行。如果表有多行，MySQL必须执行全表扫描以查找与条件匹配的行。

MySQL TIME函数

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

SELECT TIME(@dt);

执行上面查询语句，得到以下结果 -

mysql> SELECT TIME(@dt); +-----------+ | TIME(@dt) | +-----------+ | 19:41:14 | +-----------+ 1 row in set

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

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

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

执行上面查询语句，得到以下结果 -

+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+ | HOUR(@dt) | MINUTE(@dt) | SECOND(@dt) | DAY(@dt) | WEEK(@dt) | MONTH(@dt) | QUARTER(@dt) | YEAR(@dt) | +-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+ | 19 | 42 | 56 | 25 | 30 | 7 | 3 | 2017 | +-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+ 1 row in set

MySQL DATE_FORMAT函数

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

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

执行上面查询语句，得到以下结果 -

+-----------------------------------------+ | DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y') | +-----------------------------------------+ | 19:43:10 - Tuesday July 2017 | +-----------------------------------------+ 1 row in set

MySQL DATE_ADD函数

要将间隔添加到DATETIME值，请使用DATE_ADD函数，如下所示：

SET @dt = NOW(); 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';

执行上面查询语句，得到以下结果 -

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ | start | 1 second later | 1 minute later | 1 hour later | 1 day later | 1 week later | 1 month later | 1 year later | +---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ | 2017-07-25 19:43:22 | 2017-07-25 19:43:23 | 2017-07-25 19:44:22 | 2017-07-25 20:43:22 | 2017-07-26 19:43:22 | 2017-08-01 19:43:22 | 2017-08-25 19:43:22 | 2018-07-25 19:43:22 | +---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ 1 row in set

MySQL DATE_SUB函数

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

SET @dt = NOW(); 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的表，其中只有一个dt列，其数据类型为DATETIME。

USE testdb; CREATE TABLE datediff_test ( dt DATETIME );

其次，将一些行插入到datediff_test表中。

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

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

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

执行上面查询语句，得到以下结果 -

+---------------------+---------------------+ | dt | DATEDIFF(NOW(), dt) | +---------------------+---------------------+ | 2017-04-30 07:27:39 | 86 | | 2017-05-17 22:52:21 | 69 | | 2017-05-18 01:19:10 | 68 | | 2017-05-22 14:17:16 | 64 | | 2017-05-26 03:26:56 | 60 | | 2017-06-10 04:44:38 | 45 | | 2017-06-13 13:55:53 | 42 | +---------------------+---------------------+ 7 rows in set

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

上一篇： MySQL数据类型下一篇： MySQL技巧

【本文地址】

公司简介

联系我们

今日新闻

点击排行

实验室常用的仪器、试剂和: 说到实验室常用到的东西，主要就分为仪器、试剂和耗

不用再找了，全球10大实验: 01、赛默飞世尔科技（热电）Thermo Fisher Scientif

三代水柜的量产巅峰T-72坦: 作者：寞寒最近，西边闹腾挺大，本来小寞以为忙完这

通风柜跟实验室通风系统有: 说到通风柜跟实验室通风，不少人都纠结二者到底是不

集消毒杀菌、烘干收纳为一: 厨房是家里细菌较多的地方，潮湿的环境、没有完全密

实验室设备之全钢实验台如: 全钢实验台是实验室家具中较为重要的家具之一，很多

图片新闻

实验室药品柜的特性有哪些: 实验室药品柜是实验室家具的重要组成部分之一，主要

小学科学实验中有哪些教学: 计算机计算器一般打孔器打气筒仪器车显微镜

实验室各种仪器原理动图讲: 1.紫外分光光谱UV分析原理：吸收紫外光能量，引起分

高中化学常见仪器及实验装: 1、可加热仪器：2、计量仪器：（1）仪器A的名称：量

微生物操作主要设备和器具: 今天盘点一下微生物操作主要设备和器具，别嫌我啰嗦

浅谈通风柜使用基本常识: 　众所周知，通风柜功能中最主要的就是排气功能。在

MySQL datetime类型

MySQL datetime类型

今日新闻

点击排行

推荐新闻

图片新闻

专题文章