select case when date_format(a.event_time,'%H-%i-%s')<'07-00-00' then '休息时间' when date_format(a.event_time,'%H-%i-%s')<'09-00-00' then '早高峰' when date_format(a.event_time,'%H-%i-%s')<'17-00-00' then '工作时间' when date_format(a.event_time,'%H-%i-%s')<'20-00-00' then '晚高峰' when date_format(a.event_time,'%H-%i-%s')>='20-00-00' then '休息时间' end as period, count(*) as get_car_num, round(avg(timestampdiff(second,a.event_time,a.end_time)/60),1) as avg_wait_time, round(avg(if(b.mileage is null,null, timestampdiff(second,b.order_time,ifnull(b.start_time,b.finish_time))/60) ),1) as avg_dispatch_time from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id where date_format(a.event_time,'%W') not in('Saturday','Sunday') group by 1 order by 2
【mysql】
知识点:
1、时间格式。
date_format(time,'%H-%i-%s') 显示为时间,结果如09-12-44,即上午九点十二分四十四秒(二十四小时制)
2、时间差。
timestampdiff(day,start_time,end_time) 显示以天为单位,计算end_time减去start_time的差,结果如1,即相差1天
day可替换为second秒,minute分等,若使用的time值包含时分秒,而计算单位为天,则显示结果为向下取整,举例如下(建议自行尝试加深记忆)
start_time | end_time | timestampdiff(minute,start_time,end_time) |
2021-09-21 19:59:30 | 2021-09-21 20:01:00 | 实际相差1分30秒,结果单位为分,显示值为1 |
2021-09-21 19:00:30 | 2021-09-21 19:01:00 | 实际相差30秒,结果单位为分,显示值为0 |
date_format(time,'%W') 显示为星期,结果如Saturday,Sunday 等。