MySQL 查询时间字段

《MySQL 查询大于时间字段为15分钟、1小时、1天的数据》

以下代码中times为时间字段，类型为datetime

1.查询大于times十五分钟的数据

//大于号后面都是获取times十五分钟后的时间

select*from table where now() >SUBDATE(times,interval -15 minute);
select*from table where now() > SUBDATE(times,interval -900 second);
select*from table where now() > date_add(times,interval 15 minute);
select*from table where now() >ADDDATE(times,interval 15 minute);

2.查询大于times一小时的数据

//大于号后面都是获取times一小时后的时间
select*from table where now() >SUBDATE(times,interval -1 hour);
select*from table where now() > SUBDATE(times,interval -60*60 second);
select*from table where now() > date_add(times,interval -1 hour);
select*from table where now() >ADDDATE(times,interval 15 hour);

3.查询大于times一天的数据

//大于号后面都是获取times一天后的时间
select*from table where now() >SUBDATE(times,interval -1 day);
select*from table where now() > SUBDATE(times,interval -60*60*60 second);
select*from table where now() > date_add(times,interval -1 day);
select*from table where now() >ADDDATE(times,interval 15 day);

相应的如果想查询前一天的数据，加一个“-”号即可。

《Mysql 查询天、周，月，季度、年的数据》

1、获取近10天数据

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

2.获取今天数据

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

3.获取昨天数据

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

4.获取本月数据

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

5.获取上个月数据

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

6.获取本季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

7.获取上季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

 

8.获取本年数据

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

 

9.获取上年数据

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

 

10.获取本周数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

 

11.获取上周数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;