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();

I'm looking to plot hourly data on a chart. The above gets me what I want but I'd like to pad the missing hours and missing dates.

I'm assuming this is the right base query.

I'm using Laravel Eloquent format because I'll be adapting this to use ->when($site_id, function ($query) { later to filter things.

https://twitter.com/jamesmills/status/1677290025476661248

I joked about a "query as a service" for this sort of thing. Tobias pointed out this is what consultancy is. So if you want to write this for me I'll pay for your time on a screen share to get this done. DM me.

Thanks.

You need CTEs for this, but they are not available in Eloquent. Or you need the laravel-cte package.

@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.