1 、 查看当天日期
select current_date();
- 1
2、 查看当天时间
select current_time();
- 1
3、查看当天时间日期
select current_timestamp();
- 1
4、查询当天记录
select * from 表名 where to_days(时间字段名) = to_days(now());
- 1
5、查询昨天记录
select * from 表名 where to_days( now( ) ) – to_days( 时间字段名) <= 1
- 1
6、查询7天的记录
select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)
- 1
7、查询近30天的记录
select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)
- 1
8、查询本月的记录
select * from 表名 where date_format( 时间字段名, ‘%y%m’ ) = date_format( curdate( ) , ‘%y%m’ )
- 1
9、查询上一月的记录
select * from 表名 where period_diff( date_format( now( ) , ‘%y%m’ ) , date_format( 时间字段名, ‘%y%m’ ) ) =1
- 1
10、查询本季度数据
select * from 表名 where quarter(create_date)=quarter(now());
- 1
11、查询上季度数据
select * from 表名 where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter));
- 1
12、查询本年数据
select * from 表名 where year(create_date)=year(now());
- 1
13、查询上年数据
select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year));
- 1
14、查询当前这周的数据
select * from 表名 where yearweek(date_format(submittime,'%y-%m-%d')) = yearweek(now());
- 1
15、查询上周的数据
select * from 表名 where yearweek(date_format(submittime,'%y-%m-%d')) = yearweek(now())-1;
- 1
16、查询当前月份的数据
select * from 表名 where date_format(submittime,'%y-%m')=date_format(now(),'%y-%m')
- 1
17、查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
- 1
18、 查询上(几)年度数据
select * from table where year(date) = year(date_sub(now(), interval 3 year));
- 1