mysql日期有关函数的使用

清疚 2023-06-22 14:00 7阅读 0赞

mysql有许多日期相关函数，其中有一些非常好用，有时候在适当的场合用到，可以达到事半功倍的效果。

1.  统计周数据时用到   yearweek  函数，查询出今年的周数，比如20191216，查询就是201950

mysql默认 周日当做一周开始，修改成周一当做开始需要在yearweek函数后的model里面填写1

2. extract()函数用于从一个date或者interval类型中截取到特定的部分

以下为oracle的用法，oracle 不支持提取周几，但是在postgresql中可以使用extract(dow from now()) 来提取周几

extract (    
    
            { year | month | day | hour | minute | second }    
    
            | { timezone_hour | timezone_minute }    
    
            | { timezone_region | timezone_abbr }    
    
    from { date_value | interval_value } )

select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual; YEAR MONTH DAY

3. DATE\_ADD()

语法

DATE_ADD(date,INTERVAL expr type);

*date* 参数是合法的日期表达式。*expr* 参数是您希望添加的时间间隔。

type 参数可以是下列值：

<table> 
 <tbody> 
  <tr> 
   <th>Type 值</th> 
  </tr> 
  <tr> 
   <td>MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>SECOND</td> 
  </tr> 
  <tr> 
   <td>MINUTE</td> 
  </tr> 
  <tr> 
   <td>HOUR</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
  </tr> 
  <tr> 
   <td>WEEK</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
  </tr> 
  <tr> 
   <td>QUARTER</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
  </tr> 
  <tr> 
   <td>SECOND_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>MINUTE_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>MINUTE_SECOND</td> 
  </tr> 
  <tr> 
   <td>HOUR_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>HOUR_SECOND</td> 
  </tr> 
  <tr> 
   <td>HOUR_MINUTE</td> 
  </tr> 
  <tr> 
   <td>DAY_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>DAY_SECOND</td> 
  </tr> 
  <tr> 
   <td>DAY_MINUTE</td> 
  </tr> 
  <tr> 
   <td>DAY_HOUR</td> 
  </tr> 
  <tr> 
   <td>YEAR_MONTH</td> 
  </tr> 
 </tbody> 
</table>

#51.查询各学生的年龄，只按年份来算
    select student005.Sname ,year(student005.Sage) from student005;
    
    select student005.SId as 学生编号,student005.Sname  as  
    学生姓名,TIMESTAMPDIFF(YEAR,student005.Sage,CURDATE()) as 学生年龄
    from student005;
    
    #52.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
    select student005.SId as 学生编号,student005.Sname  as  
    学生姓名,TIMESTAMPDIFF(YEAR,student005.Sage,CURDATE()) as 学生年龄
    from student005;
    
    #53.查询本周过生日的学生
    select *,CURDATE()
    from student005 
    where YEARWEEK(student005.Sage)=YEARWEEK(CURDATE());
    
    select curdate(),YEARWEEK(CURDATE());-- 获取当前日期
    #54.查询下周过生日的学生
    select * from student005 
    where YEARWEEK(student005.Sage+7)=YEARWEEK(CURDATE());
    
    select *
    from student005 
    where YEARWEEK(student005.Sage)=CONCAT(YEAR(CURDATE()),week(CURDATE())+1);
    #55.查询本月过生日的学生
    select *
    from student005 
    where EXTRACT(YEAR_MONTH FROM student005.Sage)
    =EXTRACT(YEAR_MONTH FROM CURDATE());
    
    #56.查询下月过生日的学生查询下月过生日的学生
    select *
    from student005
    where EXTRACT(YEAR_MONTH FROM student005.Sage)=
    EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH));