Mysql获取指定时间范围数据

阳光穿透心脏的1/2处 2023-10-09 22:29 32阅读 0赞

MySQL获取某个时间范围内的数据 TO\_DAYS(date)函数。

to\_days()：返回从0000年至当前日期的总天数。

**目录**

1、今天(TO\_DAYS())

2、今天昨天(TO\_DAYS())

3.近7天(DATE\_SUB())

5.本月(DATE\_FORMAT())

6.上一月(PERIOD\_DIFF())

7.本季度

8.上季度

9.本年

10.去年

11.本周

12.上周

13.本月

14.距今6个月

--------------------

表数据：

![99edd98219324f13a84747af071e1f47.png][]

--------------------

#### 1、今天(TO\_DAYS()) ####

select * from 表名 where to_days(字段名) = to_days(now());

![4c4b89b7b4b5471b88a3c8195c1985b1.png][]

#### 2、今天昨天(TO\_DAYS()) ####

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS(字段名) <= 1

![f6ab979e9fdf4a7f96fa4805a2726679.png][]

#### 3.近7天(DATE\_SUB()) ####

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(字段名)

![d7589c555509467fb1c40ca2a6bb15c3.png][]

#### 5.本月(DATE\_FORMAT()) ####

SELECT * FROM 表名 WHERE DATE_FORMAT(字段名, ‘%Y%m') = DATE_FORMAT( CURDATE( ) , ‘%Y%m' )

![553324ed4feb47379bd17f4171ff922b.png][]

####  ####

#### 6.上一月(PERIOD\_DIFF()) ####

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format(字段名, ‘%Y%m') ) =1

![3ff14d5d2c5f4d7793c80f439903589a.png][]

#### 7.本季度 ####

select * from `表名` where QUARTER(字段名)=QUARTER(now());

![61aeb0866ca44a0faf506c4d0ea3cfac.png][]

#### 8.上季度 ####

select * from `表名` where QUARTER(字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

![81658d2cc54448de9783fefccdee82d1.png][]

#### 9.本年 ####

select * from `表名` where YEAR(字段名)=YEAR(NOW());

#### ![e9a77cfb9d6847c781e864fd0f7cf16c.png][]10.去年 ####

select * from `表名` where year(字段名)=year(date_sub(now(),interval 1 year));

![5cae24f132544e81806bd6dc1e4c6849.png][]

#### 11.本周 ####

SELECT * FROM 表名 WHERE YEARWEEK(date_format(字段名,'%Y-%m-%d')) = YEARWEEK(now());

![56c41537a1744a89bade8ccad86c36ec.png][]

#### 12.上周 ####

SELECT * FROM 表名 WHERE YEARWEEK(date_format(字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

![40453f9018104d41930198ef19c7f2e0.png][]

为空是因为默认按周日为第一天。

根据yearweek(date,mode)可知，第二个参数可以设置第一天是周几。

![cabb73b07678496e9a5f5042cf4bcb51.png][]

#### 13.本月 ####

select * from 表名 where date_format(字段名,'%Y-%m')=date_format(now(),'%Y-%m')

#### ![0e6113b70da849aa8dc2cc10e26f0a96.png][] 14.距今6个月 ####

select * from 表名 where 字段名 between date_sub(now(),interval 6 month) and now();

![07bdf394e8fd499293af576d7217bdde.png][]

[99edd98219324f13a84747af071e1f47.png]: https://img-blog.csdnimg.cn/99edd98219324f13a84747af071e1f47.png
[4c4b89b7b4b5471b88a3c8195c1985b1.png]: https://img-blog.csdnimg.cn/4c4b89b7b4b5471b88a3c8195c1985b1.png
[f6ab979e9fdf4a7f96fa4805a2726679.png]: https://img-blog.csdnimg.cn/f6ab979e9fdf4a7f96fa4805a2726679.png
[d7589c555509467fb1c40ca2a6bb15c3.png]: https://img-blog.csdnimg.cn/d7589c555509467fb1c40ca2a6bb15c3.png
[553324ed4feb47379bd17f4171ff922b.png]: https://img-blog.csdnimg.cn/553324ed4feb47379bd17f4171ff922b.png
[3ff14d5d2c5f4d7793c80f439903589a.png]: https://img-blog.csdnimg.cn/3ff14d5d2c5f4d7793c80f439903589a.png
[61aeb0866ca44a0faf506c4d0ea3cfac.png]: https://img-blog.csdnimg.cn/61aeb0866ca44a0faf506c4d0ea3cfac.png
[81658d2cc54448de9783fefccdee82d1.png]: https://img-blog.csdnimg.cn/81658d2cc54448de9783fefccdee82d1.png
[e9a77cfb9d6847c781e864fd0f7cf16c.png]: https://img-blog.csdnimg.cn/e9a77cfb9d6847c781e864fd0f7cf16c.png
[5cae24f132544e81806bd6dc1e4c6849.png]: https://img-blog.csdnimg.cn/5cae24f132544e81806bd6dc1e4c6849.png
[56c41537a1744a89bade8ccad86c36ec.png]: https://img-blog.csdnimg.cn/56c41537a1744a89bade8ccad86c36ec.png
[40453f9018104d41930198ef19c7f2e0.png]: https://img-blog.csdnimg.cn/40453f9018104d41930198ef19c7f2e0.png
[cabb73b07678496e9a5f5042cf4bcb51.png]: https://img-blog.csdnimg.cn/cabb73b07678496e9a5f5042cf4bcb51.png
[0e6113b70da849aa8dc2cc10e26f0a96.png]: https://img-blog.csdnimg.cn/0e6113b70da849aa8dc2cc10e26f0a96.png
[07bdf394e8fd499293af576d7217bdde.png]: https://img-blog.csdnimg.cn/07bdf394e8fd499293af576d7217bdde.png