mysql函数变量用select 赋值 mysql函数case when

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()-- 当前时间和日期

 

时间和日期的计算

时间和日期的加减

固定时间类型的加减

函数	说明
addtime()、subtime()	时间的加减
adddate()、subdate()	天数的加减
period_add()	月份的加减

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

 

自定义时间类型的加减

函数	说明
timestampadd(type,int_expr,datetime)	type：year,quarter,month,day,hour,minute,second int_expr:所要加减的数值 datetime:所要处理的时间
date_add(datetime,interval int_expr type)
date_sub(datetime,interval int_expr type)

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;

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

返回固定类型的时间差

函数	说明	备注
timediff(datetime1,datetime2)	返回两者相差的时间	其结果被限制在-838:59:59到838:59:59
datediff(datetime1,datetime2)	返回两者之间相差的天数	无
period_diff(P1,P2)	返回两者之间相差的月份	格式均为YYYYMM或者YYMM

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;