最近有个项目出现了数据统计异常,专门写一个如何处理的,填填坑。
程序一开始的代码。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 $start_date = '2020-04-20' ;$end_date = '2020-04-28' ;$data = DB::table ('between_test' ) ->select (DB::raw ('date(created_at) as date, sum(money_pay) as money_pay' )) ->whereBetween ('created_at' , [$start_date , $end_date ]) ->groupBy ('date' ) ->orderBy ('date' , 'desc' ) ->get (); $data = $data ->keyBy ('date' );$result = [];$n = 0 ;while (true ) { $date = now ()->createFromFormat ('Y-m-d' , $start_date )->addDays ($n )->format ('Y-m-d' ); if (isset ($data [$date ])) { $result [$date ] = (array ) $data [$date ]; } else { $result [$date ] = [ 'date' => $date , 'money_pay' => 0 , ]; } if ($date === $end_date ) { break ; } $n ++; }
通过上面假数据可以看到最后一天是有数据的,然后这里一直都是 0。
清空比较急,当时做法是查询的日期多加一天,数据处理最后会判断是不是到终止日期了,这边的修改也不会导致返回的数据多了一天。
1 2 3 whereBetween ('created_at' , [$start_date , $end_date ])whereBetween ('created_at' , [$start_date , date ('Y-m-d' , strtotime ($end_date ) + (60 * 60 * 24 ))])
为了更好的排查,我们把 SQL 语句打印出来。
1 select date (created_at) as date , sum (money_pay) as money_pay from `between_test` where `created_at` between '2020-04-20' and '2020-04-28' group by `date ` order by `date ` desc ;
通过生成的 SQL 语句可以看到我们的日期是没有时间的,会缺省为 00:00:00
,我们也可以在日期后面加上 23:59:59
,这个是一天最后的时间,记得和日期直接要有空格隔开,不然无法转换识别到。
1 whereBetween ('created_at' , [$start_date , $end_date . ' 23:59:59' ])
又引发了来一个问题,假设一条数据刚刚好是最后一天、最后一秒,如 2020-04-28 23:59:59
,那这条是否也在范围内呢?专门修改了条数据进行测试,结果是在范围内的。
还有另外一个办法,我们不使用 whereBetween
,换成 whereDate
来进行查询,看起来可能会更容易理解。
1 2 3 4 whereBetween ('created_at' , [$start_date , $end_date ])whereDate ('created_at' , '>=' , $start_date ) ->whereDate ('created_at' , '<=' , $end_date )