Fill Gaps in Statistical Time Series - Date by the hour

mysql.php

$requests = DB::table('advert_requests as ar')
    ->select(DB::raw("date_format(ar.created_at, '%Y-%m-%d %H' ) AS grouped_hour, count(*) AS requests"))
    ->join('placements as p', 'p.id', '=', 'ar.placement_id')
    ->where('ar.created_at', '>=', $from)
    ->where('p.site_id', $site_id)
    ->groupBy('grouped_hour')
    ->get();

@tpetry is correct. The other option is to fill a table with every hour in the year and then join it. Kinda gross, but would work. It's only 87,600 rows to cover ten years, so it's barely any data at all.

OK, so forgive my ignorance here...

It looks like there is a package to do something with CTE's but not dates/hours https://github.com/staudenmeir/laravel-cte

AI said below which doesn't work and I don't understand...

// Define the recursive CTE query
$recursiveCTE = DB::raw('WITH RECURSIVE cte AS (
    SELECT :from AS grouped_date, 0 AS grouped_hour
    UNION ALL
    SELECT cte.grouped_date, cte.grouped_hour + 1
    FROM cte
    WHERE cte.grouped_hour < 23
)');

$requests = DB::table('advert_requests as ar')
    ->select(DB::raw('cte.grouped_date, cte.grouped_hour, COUNT(ar.created_at) AS requests'))
    ->leftJoin('placements as p', 'p.id', '=', 'ar.placement_id')
    ->join(DB::raw('cte'), function ($join) {
        $join->on(DB::raw('DATE(ar.created_at)'), '=', DB::raw('cte.grouped_date'))
            ->on(DB::raw('HOUR(ar.created_at)'), '=', DB::raw('cte.grouped_hour'));
    })
    ->where('ar.created_at', '>=', DB::raw(':from'))
    ->where('p.site_id', DB::raw(':site_id'))
    ->groupBy('cte.grouped_date', 'cte.grouped_hour')
    ->orderBy('cte.grouped_date', 'asc')
    ->orderBy('cte.grouped_hour', 'asc')
    ->setBindings([
        'from' => $from,
        'site_id' => $site_id,
    ])
    ->get(['cte.grouped_date', 'cte.grouped_hour', 'requests']);

So should I just use RAW SQL? If the answer is yes, I'd like to pay for someone's time to help me get it right... anyone?

I really have no idea what I'm doing.

You can't build the SQL query like this because the WITH ... needs to proceed everything. staudenmeier/laravel-cte is good. I've implemented CTEs in my PG driver the same way like he did.

We can do a session again some time. Send me a DM on Twitter if you want to.