当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL中, 如何查询某一天, 某一月, 某一年的数据

MySQL中, 如何查询某一天, 某一月, 某一年的数据

2019年06月12日  | 移动技术网IT编程  | 我要评论 
转自：https://www.cnblogs.com/azhqiang/p/6702950.html
今天
复制代码
select * from 表名 where to_days(时间字段名) = to_days(now());


昨天(包括昨天和今天的数据)




复制代码
select * from 表名 where to_days( now( ) ) - to_days( 时间字段名) <= 1




昨天(只包括昨天)




复制代码
select * from 表名 where datediff(字段,now())=-1; -- 同理，查询前天的就是-2




近7天




复制代码
select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)




近30天




复制代码
select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)




本月




复制代码
select * from 表名 where date_format( 时间字段名, '%y%m' ) = date_format( curdate( ) , '%y%m' )




上一月




复制代码
select * from 表名 where period_diff( date_format( now( ) , '%y%m' ) , date_format( 时间字段名, '%y%m' ) ) =1




查询本季度数据




复制代码
select * from `ht_invoice_information` where quarter(create_date)=quarter(now());




查询上季度数据




复制代码
select * from `ht_invoice_information` where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter));




查询本年数据




复制代码
select * from `ht_invoice_information` where year(create_date)=year(now());




查询上年数据




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




查询当前这周的数据




复制代码
select name,submittime from enterprise where yearweek(date_format(submittime,'%y-%m-%d')) = yearweek(now());




查询上周的数据




复制代码 
select name,submittime from enterprise where yearweek(date_format(submittime,'%y-%m-%d')) = yearweek(now())-1;




查询上个月的数据




复制代码


 


　select name,submittime from enterprise where date_format(submittime,'%y-%m')=date_format(date_sub(curdate(), interval 1 month),'%y-%m')


select * from user where date_format(pudate,'%y%m') = date_format(curdate(),'%y%m') ; 

select * from user where weekofyear(from_unixtime(pudate,'%y-%m-%d')) = weekofyear(now()) 

select * from user where month(from_unixtime(pudate,'%y-%m-%d')) = month(now()) 

select * from user where year(from_unixtime(pudate,'%y-%m-%d')) = year(now()) and month(from_unixtime(pudate,'%y-%m-%d')) = month(now()) 

select * from user where pudate between  上月最后一天  and 下月第一天 




查询当前月份的数据 




复制代码
select name,submittime from enterprise where date_format(submittime,'%y-%m')=date_format(now(),'%y-%m')




查询距离当前现在6个月的数据




复制代码
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();




                    

                        

                            
                        

                    

                    


                    
                    

                        
    
                            
  • 
                                
                            
    • 
                        

                    


                    
                    

                    

                        
您可能感兴趣的文章:

                        
                    

                    

                        如对本文有疑问，
                        点击进行留言回复！！
                    

                

                 
                

                    
相关文章:

                    
                

               
                 
                 

                

                    
                    

                        
                        
                        

                        

                            

                                
                                    
                                
                                 
                                    
                                
                            

                            

                                验证码：
                                
                                    
                                    
                                    
                                
                            

                        

                        
                    

                    
                    

                        
                        
                        
                    

                

            

        

        
        
 
            

                

                    
                    

                    
                


                

                    

                        

                            
最近更新的文章

                            

                                
                            

                        

                    

                


                

                    
                    

                        
                    

                


                

                

                    

                        

                            
大家感兴趣的文章

                            

                                
                            

                        

                    

                

                

                    
                    

                        
                    

                


            

        

    



 


 
 

     
    

        

移动技术网