MySQL datediff function not available in SQLite used for unit tests

I have used the following Raw query in my Laravel project.

$statisticsInRangeDate = $myModel->selectRaw(
    "floor(datediff(created_at, '{$dateInfo['currentRangeDate']}')".
    " / {$dateInfo['daysRange']}) * {$dateInfo['daysRange']} AS diff_days_range, count(*) as total_clicks, ".
    'min(created_at) ,max(created_at)'
)
    ->groupByRaw('diff_days_range')
    ->orderByRaw('diff_days_range DESC')
    ->get();

The raw query is:

select
    floor(datediff(created_at, '2023-04-04') / 3) * 3 AS diff_days_range,
    count(*) as total_install,
    min(created_at),
    max(created_at) 
from sdk_clicks 
where sdk_clicks.application_id = 1
    and sdk_clicks.application_id is not null
    and created_at >= '2023-04-01'
    and created_at < '2023-04-07'
    and sdk_clicks.deleted_at is null
group by diff_days_range
order by diff_days_range

The database of the project is mysql and I get the necessary output, but I get the following error when running the phpunit tests which use a sqlite memory database for speed.

> Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 no such function: datediff (SQL: select floor...

I have the knowledge that this function does not exist in sqlite and julianday('now') - julianday(DateCreated) should be used for the time difference, but I am looking for the best solution to solve this problem automatically by detecting the type of database and the appropriate function be used.

Note: Due to the need for high speed test, I want to use memory and sqlite database

Considering the possibility of using Macro in laravel, it allows me to add new functions to Illuminate\Database\Eloquent\Builder.

On my AppServiceProvider.php I create the macro rangeDateDiff() :

Builder::macro('rangeDateDiff',
            function ($firstDate, $secondDate, $daysRange = 1, string $as = 'diff_days_range') {
            if ($this->getConnection()->getDriverName() == 'sqlite') {
                $this->addSelect(\DB::raw(
                    "floor (cast (julianday(strftime('%Y-%m-%d', {$firstDate})) - julianday(strftime('%Y-%m-%d', {$secondDate})) As Integer)"
                    . " / {$daysRange}) * {$daysRange} AS {$as}")
                );
            } else {
                $this->addSelect(\DB::raw(
                    "floor(datediff({$firstDate}, {$secondDate})"
                    . " / {$daysRange}) * {$daysRange} AS {$as}")
                );
            }

            return $this;
        });

then use this new function globaly in Eloquent model :

$sdkClicks = $this->application->sdkClicks()
            ->where('created_at', '>=', $dateInfo['previousRangeDate'])
            ->where('created_at', '<', $dateInfo['toDate']->format('Y-m-d'));

        $sdkClicks->rangeDateDiff('created_at', "'{$dateInfo['currentRangeDate']}'", $dateInfo['daysRange'])
            ->groupByRaw('diff_days_range')
            ->orderByRaw('diff_days_range DESC')->get();

The advantage of this method is that it's more clean, and most importantly, if another type of database needs to be used in the project, the code is more maintainable.

PHP provides the ability to create user defined functions in sqlite. This is done using the PDO::sqliteCreateFunction() function.

Basically, you're defining a function inside of sqlite that will call the PHP code you provide.

Your code would look something like:

$pdo = DB::connection()->getPdo();

// Only do this if using sqlite.
if ($pdo->getAttribute(PDO::ATTR_DRIVER_NAME) != 'sqlite') {
    return;
}

$pdo->sqliteCreateFunction(
    'datediff', // name of the sqlite function to create
    function ($first, $second) {
        $dateFirst = Carbon\Carbon::parse($first)->startOfDay();
        $dateSecond = Carbon\Carbon::parse($second)->startOfDay();
        
        return $dateSecond->diffInDays($dateFirst, false);
    }, // PHP callback function implementation
    2 // number of parameters the sqlite function takes
);

Add this somewhere in your test suite before the query is executed, and you should be good.