关于sql时间方面的处理

查询大于时间两小时（例：订单设置两小时后过期

$res = Order::where(['status'=>0,'sid'=>1])->whereRaw("created_at < NOW() - INTERVAL 2 HOUR")->get();

时间字段< NOW() - INTERVAL 2 HOUR

解读：当前时间减去2小时（HOUR）如果还大于下单时间则超过

查询当天、昨天等数据 用于统计

public function senior(Request $request)
    {
        $Sid = $request->session()->get('SUserId');
        if ($request->ajax()){
            //查询今天订单数据
            $dateD = date('Y-m-d');
            //我也看不懂。。。有时间研究
            $data = DB::select(
                "SELECT IF(count IS NULL, 0, count) as num FROM (SELECT count(*) AS count,DATE_FORMAT(created_at, '%H') AS hour
      FROM lkx_orders where date_format(created_at,'%Y-%m-%d') = '$dateD' GROUP BY hour ORDER BY 1) A
  RIGHT JOIN (SELECT one.hours + two.hours AS dayHour
              FROM (SELECT 0 hours
                    UNION ALL SELECT 1 hours
                    UNION ALL SELECT 2 hours
                    UNION ALL SELECT 3 hours
                    UNION ALL SELECT 4 hours
                    UNION ALL SELECT 5 hours
                    UNION ALL SELECT 6 hours
                    UNION ALL SELECT 7 hours
                    UNION ALL SELECT 8 hours
                    UNION ALL SELECT 9 hours) one
                CROSS JOIN (SELECT 0 hours UNION ALL SELECT 10 hours UNION ALL SELECT 20 hours) two
              WHERE (one.hours + two.hours) < 24) B ON A.hour = CONVERT(B.dayHour, SIGNED) ORDER BY dayHour"
            );
            $orderNum = array_column($data,'num');

            $data7D = date('Y-m-d',strtotime("-6 day"));

            $data = DB::select("
            SELECT t1.date_str , COALESCE(t2.date_total_countss,0) as date_total_count
                FROM(
                    SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str
                    FROM (SELECT @cdate:=date_add(CURDATE(),interval + 1 day) from lkx_orders) tmp1
                    WHERE date_format(@cdate,'%Y-%m-%d') >'$data7D'
                ) t1
                LEFT JOIN(
                    SELECT date_format(m.created_at, '%Y-%m-%d') as date_str , sum(price) as date_total_countss
                    FROM lkx_orders as m
                    WHERE date_format(m.created_at,'%Y-%m-%d') >'$data7D' and m.status=1 and sid=$Sid
                    GROUP BY date_str
                ) t2
                on t1.date_str = t2.date_str
                order by t1.date_str asc
            ");


            $price7D['data'] = array_column($data,'date_total_count');
            $price7D['title'] = array_column($data,'date_str');

            $dataMyM = date("Y-m-d",mktime(0, 0 , 0,date("m"),2,date("Y")));

            $data = DB::select("
            SELECT t1.date_str , COALESCE(t2.date_total_countss,0) as date_total_count
                FROM(
                    SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str
                    FROM (SELECT @cdate:=date_add(CURDATE(),interval + 1 day) from lkx_orders) tmp1
                    WHERE date_format(@cdate,'%Y-%m-%d') >= '$dataMyM'
                ) t1
                LEFT JOIN(
                    SELECT date_format(m.created_at, '%Y-%m-%d') as date_str , sum(price) as date_total_countss
                    FROM lkx_orders as m
                    WHERE date_format(m.created_at,'%Y-%m-%d') >= '$dataMyM' and m.status=1 and sid=$Sid
                    GROUP BY date_str
                ) t2
                on t1.date_str = t2.date_str
                order by t1.date_str asc
            ");

            $priceMyM['data'] = array_column($data,'date_total_count');
            $priceMyM['title'] = array_column($data,'date_str');

            //获取上个月数据统计
            $dataUpM = date("Y-m-d",mktime(0, 0 , 0,date("m")-1,30,date("Y")));

            $Mnum = date("t",strtotime("-1 month"));

            $dataUpYm = date('Y-m',strtotime("-1 month"));

            $data = DB::select("
            SELECT t1.date_str , COALESCE(t2.date_total_countss,0) as date_total_count
                FROM(
                    SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str
                    FROM (SELECT @cdate:=date_add('$dataUpM',interval + 1 day) from lkx_orders) tmp1
                    limit $Mnum
                ) t1
                LEFT JOIN(
                    SELECT date_format(m.created_at, '%Y-%m-%d') as date_str , sum(price) as date_total_countss
                    FROM lkx_orders as m
                    WHERE date_format(m.created_at,'%Y-%m') = '$dataUpYm'
                    GROUP BY date_str
                ) t2
                on t1.date_str = t2.date_str
                order by t1.date_str asc
            ");

            $priceUpM['data'] = array_column($data,'date_total_count');
            $priceUpM['title'] = array_column($data,'date_str');

            return array(
                'orderNum'=>$orderNum,
                'priceMyM'=>$priceMyM,
                'price7D'=>$price7D,
                'priceUpM'=>$priceUpM,
            );
        }
    }

新手经百度查询等方式写的、如有更好方案请指点一番...