根据业务需求查出所需数据: $data = DB::table('course_study_record') ->whereBetween('created_at',[$start_time,$end_time]) ->groupBy('date') ->select([DB::raw('FROM_UNIXTIME(created_at,"%Y-%m-%d") as date'),DB::raw('COUNT(id) as value')]) ->distinct() ->get();
array:6 [ 0 => array:2 [ "date" => "2019-06-14" "value" => 6 ] 1 => array:2 [ "date" => "2019-06-15" "value" => 5 ] 2 => array:2 [ "date" => "2019-06-16" "value" => 1 ] 3 => array:2 [ "date" => "2019-06-17" "value" => 2 ] 4 => array:2 [ "date" => "2019-06-18" "value" => 1 ] 5 => array:2 [ "date" => "2019-06-19" "value" => 1 ] ]
/** * 按天统计时补全天数并返回格式化数据结构 * @param $day 需要统计的一共天数 * @param $endtime 结束时间 * @param $arr 要补全的数组 * @return array */ function pad_day($day,$endtime,$arr){ //今天结束时间 $endToday=mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1; $diff_days= $endToday-$endtime; $diff_days=ceil($diff_days/86400) - 1; $new = [];//用来存放格式化后的新数组 if(!empty($arr)){ foreach ($arr as $v){ $new[$v['date']]['date'] = $v['date']; $new[$v['date']]['num'] = $v['value']; } } for ($i = 0; $i < $day; $i++) { $diff_day=$diff_days+$i; $date = date('Y-m-d', strtotime('-' . $diff_day . 'day')); if(!array_key_exists($date,$new)){ $new[$date] = ['date'=>$date,'num'=>0]; } } ksort($new); return array_values($new); }