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)-- 返回时分秒FROMtime_function;
跟周有关的函数SELECTcreate_time,week(create_time)-- 返回所在周数(本年遇到的第一个星期天作为第一周,前面的计算为第0周),week(create_time,1)-- 返回所在周数(第一周超过3天,那么计算为本年的第一周),weekofyear(create_time)-- 返回所在周数,yearweek(create_time)-- 返回年份和周数,weekday(create_time)-- 返回工作日索引值(0代表周一),dayofweek(create_time)-- 返回工作日索引值(1代表周日)FROMtime_function;
所属第几天SELECTcreate_time,dayofmonth(create_time)-- 返回月份的第几天,dayofyear(create_time)-- 返回年份的第几天FROMtime_function;
返回英文名SELECTcreate_time,dayname(create_time)-- 返回工作日英文名,monthname(create_time)-- 返回月份英文名FROMtime_function;
返回特定的格式SELECTcreate_time,date_format(create_time,"%Y-%m-%d %H:%i:%s")-- 返回年月日时分秒,date_format(create_time,"%w")-- 返回所在工作日索引值(0代表周日),time_format(create_time,"%H:%i")-- 返回时分FROMtime_function;
当前时间和日期SELECTcurrent_date()-- 当前日期,current_time-- 当前时间,now()-- 当前时间和日期,localtime()-- 当前时间和日期
时间和日期的计算
时间和日期的加减
固定时间类型的加减函数说明
addtime()、subtime()时间的加减
adddate()、subdate()天数的加减
period_add()月份的加减SELECTcreate_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)SELECTcreate_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-- 减少一小时FROMtime_function;
返回两个时间格式之间的差值
返回固定类型的时间差函数说明备注
timediff(datetime1,datetime2)返回两者相差的时间其结果被限制在-838:59:59到838:59:59
datediff(datetime1,datetime2)返回两者之间相差的天数无
period_diff(P1,P2)返回两者之间相差的月份格式均为YYYYMM或者YYMMSELECTcreate_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-- 返回两者之间的月份差值FROMtime_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-- 返回两者之间的天数差值FROMtime_function;来源:https://www.icode9.com/content-2-849751.html