MySQL case when then 语句使用和时间函数使用

怼烎@ 2021-09-08 19:14 498阅读 0赞

**Laravel上使用：**

$list = Article::where('status',2)->where('category\_id',$category\_id)  
                ->select(DB::raw('id, type,thumb\_img,title,tags,intro,video\_id,external\_link  
                    ,live\_start\_time,live\_end\_time,live\_id,page\_views,zan\_num,published\_at,collection\_num, case when collection\_num=0 then timestampdiff(second,now(),live\_start\_time) else timestampdiff(second,live\_start\_time,now()) end as sort\_time'))  
                    ->orderBy('collection\_num','asc')  
                    ->orderBy('sort\_time','asc')  
                    ->paginate($per\_page);

**原生MySQL语句：**

SELECT id, type,thumb\_img,title,tags,intro,video\_id,external\_link,live\_start\_time,live\_end\_time,live\_id,page\_views,zan\_num,published\_at,status , case when status =0 then timestampdiff(second,now(),live\_start\_time) else timestampdiff(second,live\_start\_time,now()) end as sort\_time FROM \`articles\` WHERE category\_id=4 ORDER BY status asc,sort\_time asc;

**返回部分值**

返回年、季度、月、日、时、分、秒、年月日、时分秒

SELECT create_time -- 时间    ,YEAR(create_time)-- 返回年    ,QUARTER(create_time)-- 返回季度    , MONTH(create_time)-- 返回月    , DAY(create_time)-- 返回日    , HOUR(create_time)-- 返回小时    , MINUTE(create_time)-- 返回分钟    , SECOND(create_time)-- 返回秒    , DATE(create_time)-- 返回年月日    , TIME(create_time)-- 返回时分秒FROM    time_function;

**跟周有关的函数**

SELECT     create_time     ,week(create_time)-- 返回所在周数（本年遇到的第一个星期天作为第一周，前面的计算为第0周）    ,week(create_time,1)-- 返回所在周数（第一周超过3天，那么计算为本年的第一周）    ,weekofyear(create_time)-- 返回所在周数    ,yearweek(create_time)-- 返回年份和周数    ,weekday(create_time)-- 返回工作日索引值（0代表周一）    ,dayofweek(create_time)-- 返回工作日索引值（1代表周日）FROM    time_function;

**所属第几天**

SELECT     create_time     ,dayofmonth(create_time)-- 返回月份的第几天    ,dayofyear(create_time)-- 返回年份的第几天FROM    time_function;

**返回英文名**

SELECT     create_time     ,dayname(create_time)-- 返回工作日英文名    ,monthname(create_time)-- 返回月份英文名FROM    time_function;

**返回特定的格式**

SELECT     create_time     ,date_format(create_time,'%Y-%m-%d %H:%i:%s')-- 返回年月日时分秒    ,date_format(create_time,'%w')-- 返回所在工作日索引值（0代表周日）    ,time_format(create_time,'%H:%i')-- 返回时分FROM    time_function;

**当前时间和日期**

SELECT current_date()-- 当前日期,current_time-- 当前时间,now()-- 当前时间和日期,localtime()-- 当前时间和日期

**时间和日期的计算**

时间和日期的加减

固定时间类型的加减

<table> 
 <tbody> 
  <tr> 
   <td colspan="1" style="vertical-align:top;">函数</td> 
   <td colspan="1" style="vertical-align:top;">说明</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">addtime()、subtime()</td> 
   <td style="vertical-align:top;">时间的加减</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">adddate()、subdate()</td> 
   <td style="vertical-align:top;">天数的加减</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">period_add()</td> 
   <td style="vertical-align:top;">月份的加减</td> 
  </tr> 
 </tbody> 
</table>

SELECT create_time,addtime(create_time,'1:00:00') as add1hour-- 增加一小时,subtime(create_time,'1:00:00') as reduce1hour-- 减少一小时,adddate(create_time,1) as add1day-- 增加一天,subdate(create_time,1) as reduce1day-- 减少一天,period_add(date_format(create_time,'%Y%m'),1) as add1month-- 增加一个月,period_add(date_format(create_time,'%Y%m'),-1) as reduce1month-- 减少一个月from time_function

**自定义时间类型的加减**

<table> 
 <tbody> 
  <tr> 
   <td colspan="1" style="vertical-align:top;">函数</td> 
   <td colspan="1" style="vertical-align:top;">说明</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">timestampadd(type,int_expr,datetime)</td> 
   <td colspan="1" style="vertical-align:top;"> <p>type：year,quarter,month,day,hour,minute,second</p> <p>int_expr:所要加减的数值</p> <p>datetime:所要处理的时间</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">date_add(datetime,interval int_expr type)</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">date_sub(datetime,interval int_expr type)</td> 
  </tr> 
 </tbody> 
</table>

SELECT     create_time    ,TIMESTAMPADD(day,1,create_time) as add1day-- 增加一天    ,TIMESTAMPADD(day,-1,create_time) as reduce1day-- 减少一天    ,date_add(create_time,interval 1 hour) as add1hour_1-- 增加一小时    ,date_add(create_time,interval -1 hour) as reduce1hour_1-- 减少一小时    ,date_sub(create_time,interval -1 hour) as add1hour_2-- 增加一小时    ,date_sub(create_time,interval 1 hour) as reduce1hour_2-- 减少一小时FROM    time_function;

**返回两个时间格式之间的差值**

返回固定类型的时间差

<table> 
 <tbody> 
  <tr> 
   <td colspan="1" style="vertical-align:top;">函数</td> 
   <td colspan="1" style="vertical-align:top;">说明</td> 
   <td colspan="1" style="vertical-align:top;">备注</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">timediff(datetime1,datetime2)</td> 
   <td style="vertical-align:top;">返回两者相差的时间</td> 
   <td style="vertical-align:top;">其结果被限制在<code>-838:59:59</code>到<code>838:59:59</code></td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">datediff(datetime1,datetime2)</td> 
   <td style="vertical-align:top;">返回两者之间相差的天数</td> 
   <td style="vertical-align:top;">无</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">period_diff(P1,P2)</td> 
   <td style="vertical-align:top;">返回两者之间相差的月份</td> 
   <td style="vertical-align:top;">格式均为YYYYMM或者YYMM</td> 
  </tr> 
 </tbody> 
</table>

SELECT     create_time    ,timediff('2019-03-01 16:00:00',create_time) as date_diff-- 返回两者之间相差的时间    ,now()-- 当前时间    ,datediff(now(),create_time) as date_diff-- 返回两者之间相差的天数    ,date_format(now(),'%Y%m')-- 当前月份    ,period_diff(date_format(now(),'%Y%m'),date_format(create_time,'%Y%m')) as month_diff-- 返回两者之间的月份差值FROM    time_function;

**返回自定义类型的时间差**

TIMESTAMPDIFF(type,datetime1,datetime2)

SELECT create_time    ,now()    ,timestampdiff(month,create_time,now()) as month_diff-- 返回两者之间的月份差值    ,timestampdiff(day,create_time,now()) as day_diff-- 返回两者之间的天数差值FROM    time_function;