MySQL日期及时间字段怎么查询

1.日期和时间类型概览

MySQL支持的日期和时间类型有 DATETIME、TIMESTAMP、DATE、TIME、YEAR ,

几种类型比较如下:

MySQL日期及时间字段查询:格式转换、比较和计算

涉及到日期和时间字段类型选择时,根据存储需求选择合适的类型即可。

2.日期和时间相关函数

处理日期和时间字段的函数有很多,有的经常会在查询中使用到,

下面介绍下几个相关函数的使用方法:

  • CURDATE 和 CURRENT_DATE 两个函数作用相同,返回当前系统的日期值。

  • CURTIME 和 CURRENT_TIME 两个函数作用相同,返回当前系统的时间值。

  • NOW() 和 SYSDATE() 两个函数作用相同,返回当前系统的日期和时间值。

  • UNIX_TIMESTAMP 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数。

  • FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数。

  • TO_DAYS() 提取日期值并返回自公元0年到现在的天数。

  • DAY() 获取指定日期或时间中的天值。

  • DATE() 获取指定日期或时间中的日期。

  • TIME() 获取指定日期或时间中的时间。

  • MONTH 获取指定日期中的月份。

  • WEEK 获取指定日期是一年中的第几周。

  • YEAR 获取年份。

  • QUARTER 获取日期所在的季度值。

  • DATE_ADD 和 ADDDATE 两个函数功能相同,都是向日期添加指定的时间间隔。

  • DATE_SUB 和 SUBDATE 两个函数功能相同,都是向日期减去指定的时间间隔。

  • ADDTIME 时间加法运算,在原始时间上添加指定的时间。

  • SUBTIME 时间减法运算,在原始时间上减去指定的时间。

  • DATEDIFF 获取两个日期之间间隔,返回参数 1 减去参数 2 的值。

  • DATE_FORMAT 格式化指定的日期,根据参数返回指定格式的值。

一些使用示例:

mysql>
select CURRENT_DATE,CURRENT_TIME,NOW();

+--------------+--------------+---------------------+
| CURRENT_DATE | CURRENT_TIME | NOW() |
+--------------+--------------+---------------------+
| 2020-06-03 | 15:09:37 | 2020-06-03 15:09:37 |
+--------------+--------------+---------------------+

mysql>
select TO_DAYS('
2020-06-03 15:09:37'
),
TO_DAYS('
2020-06-03'
)-TO_DAYS('
2020-06-01'
);

+--------------------------------+---------------------------------------------+
| TO_DAYS('
2020-06-03 15:09:37'
) | TO_DAYS('
2020-06-03'
)-TO_DAYS('
2020-06-01'
) |
+--------------------------------+---------------------------------------------+
| 737944 | 2 |
+--------------------------------+---------------------------------------------+

mysql>
select MONTH('
2020-06-03'
),WEEK('
2020-06-03'
),YEAR('
2020-06-03'
);

+---------------------+--------------------+--------------------+
| MONTH('
2020-06-03'
) | WEEK('
2020-06-03'
) | YEAR('
2020-06-03'
) |
+---------------------+--------------------+--------------------+
| 6 | 22 | 2020 |
+---------------------+--------------------+--------------------+

# DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数
mysql>
SELECT DATEDIFF('
2017-11-30'
,'
2017-11-29'
) AS COL1,
->
DATEDIFF('
2017-11-30'
,'
2017-12-15'
) AS col2;

+------+------+
| COL1 | col2 |
+------+------+
| 1 | -15 |
+------+------+ 3.日期和时间字段的规范查询

为满足我们的查询需求做好准备,项目需求通常涉及基于日期或时间的条件筛选查询。下面我们来探讨如何查询和筛选日期和时间字段,因为这类需求是多种多样的。

首先,为了使查询更加准确,在插入数据时也要按规范来插入。为了确保年份使用4位数字且日期和月份在合理范围内,我们创建了一个表并插入了部分数据以方便测试。

CREATE TABLE `t_date` (
`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '
自增主键'
,
`year_col` YEAR NOT NULL COMMENT '
年'
,
`date_col` date NOT NULL COMMENT '
日期'
,
`time_col` time NOT NULL COMMENT '
时间'
,
`dt_col` datetime NOT NULL COMMENT '
datetime时间'
,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '
创建时间'
,
PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='
time测试表'
;

# 日期和时间都选取当前的日期或时间
INSERT INTO t_date (year_col,date_col,time_col,dt_col,create_time) VALUES
(year(now()),DATE(NOW()),time(now()),NOW(),NOW());

# 指定日期或时间插入
INSERT INTO t_date ( `year_col`, `date_col`, `time_col`, `dt_col`, `create_time` )
VALUES
( 2020, '
2020-06-03'
, '
09:00:00'
, '
2020-06-03 10:04:04'
, '
2020-06-03 10:04:04'
),
( 2020, '
2020-05-10'
, '
18:00:00'
, '
2020-05-10 16:00:00'
, '
2020-05-10 16:00:00'
),
( 2019, '
2019-10-03'
, '
16:04:04'
, '
2019-10-03 16:00:00'
, '
2019-10-03 16:00:00'
),
( 2018, '
2018-06-03'
, '
16:04:04'
, '
2018-06-03 16:00:00'
, '
2018-06-03 16:00:00'
),
( 2000, '
2000-06-03'
, '
16:04:04'
, '
2000-06-03 08:00:00'
, '
2000-06-03 08:00:00'
),
( 2008, '
2008-06-03'
, '
16:04:04'
, '
2008-06-03 08:00:00'
, '
2008-06-03 08:00:00'
),
( 1980, '
1980-06-03'
, '
16:04:04'
, '
1980-06-03 08:00:00'
, '
1980-06-03 08:00:00'
);

根据上面测试表的数据,我们来学习下几种常见查询语句的写法:

根据日期或时间等值查询:

select * from t_date where year_col = 2020;

select * from t_date where date_col = '
2020-06-03'
;

select * from t_date where dt_col = '
2020-06-03 16:04:04'
;

根据日期或时间范围查询:

select * from t_date where date_col >
'
2018-01-01'
;

select * from t_date where dt_col >
= '
2020-05-01 00:00:00'
and dt_col <
'
2020-05-31 23:59:59'
;

select * from t_date where dt_col between '
2020-05-01 00:00:00'
and '
2020-05-31 23:59:59'
;

查询本月的数据:

# 查询create_time在本月的数据
select * from t_date where DATE_FORMAT(create_time, '
%Y-%m'
) = DATE_FORMAT( CURDATE( ) , '
%Y-%m'
);

查询最近多少天的数据:

# 以date_col为条件 查询最近7天或30天的数据
SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <
= date(date_col);

SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <
= date(date_col);

其他各类查询写法:

# 查询今天的数据
SELECT * FROM t_date WHERE TO_DAYS(create_time) = TO_DAYS(NOW());

# 查询某个月的数据
SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '
%Y-%m'
)='
2020-06'
;

# 查询某年的数据
SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '
%Y'
)= 2020;

SELECT * FROM t_date WHERE YEAR(create_time) = 2020;

# 根据日期区间查询数据,并排序
SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '
%Y'
) BETWEEN '
2018'
AND '
2020'
ORDER BY create_time DESC;


MySQL是一个广泛使用的关系型数据库管理系统,其中日期和时间字段的处理非常重要。在日常使用中,需要对日期和时间字段进行各种查询操作,包括格式转换、比较和计算。这篇文章将介绍如何使用MySQL查询操作这些常见的日期和时间字段。
一、格式转换:日期时间格式化
对于开发人员来说,转换日期和时间的格式非常重要。MySQL提供了一些内置函数来转换日期和时间的格式,包括DATE_FORMAT()、STR_TO_DATE()和TIME_FORMAT()。这些函数可以将日期和时间字段的原始格式转换为人类可读的格式。例如,使用DATE_FORMAT()函数可以将日期字段按照指定格式转换为字符串,如下例所示:
SELECT DATE_FORMAT('2021-07-28', '%Y年%m月%d日');
输出的结果为“2021年07月28日”。在使用这些函数时,需要注意使用正确的格式选项,避免转换出现错误。
二、比较:日期时间的比较查询
在MySQL中,比较日期和时间字段的大小是常见的操作。基本操作包括等于、大于、小于、大于等于和小于等于。例如,查询特定日期之后的记录,可以使用大于符号(“>”),如下例所示:
SELECT * FROM table_name WHERE date_column > '2021-07-01';
这条语句将返回所有日期字段大于“2021-07-01”的记录。当使用时间字段进行比较时,需要确保使用正确的格式,以避免比较错误。有时候,还需要比较两个日期或时间字段的差异,可以使用DATEDIFF()、TIMESTAMPDIFF()等函数计算日期或时间之间的差异。
三、计算:日期时间计算查询
在MySQL中,还可以对日期和时间字段进行各种计算操作。例如,对日期字段进行加减操作,可以使用DATE_ADD()和DATE_SUB()函数,如下例所示:
SELECT DATE_ADD('2021-07-01', INTERVAL 1 MONTH);
这条语句将返回一个新的日期,该日期为“2021-08-01”。另外,还可以使用其他函数,如YEAR()、MONTH()、DAY()、DATE()等,来提取日期和时间字段的年、月、日、小时、分钟等部分。
总结
本文介绍了MySQL日期和时间字段的三种常见操作:格式转换、比较和计算,每种操作都有对应的函数和语法。在实际应用中,可以根据具体需求选择合适的操作和函数来操作日期和时间字段。使用这些方法可以使查询更加高效和精确。