SQLSTATE[42883]: Undefined function: 7 ERROR: function date_format(date, unknown) does not exist

Question

I created this to compare is time now the same as time in my table row executes_at.

$dateNow = Carbon::now()->format('Y-m-d');
$hourNow = Carbon::now()->format('H');
$minuteNow = Carbon::now()->format('i');

$recordings = Recording::with('therapy')
        ->where(DB::raw("DATE_FORMAT(executes_at,'%Y-%m-%d')"), '=', $dateNow)
        ->where(DB::raw("DATE_FORMAT(executes_at,'%H')"), '=', $hourNow)
        ->where(DB::raw("DATE_FORMAT(executes_at,'%i')"), '=', $minuteNow)
        ->get();

and it worked in MySQL but because now we use PostgreSQL I have this error

SQLSTATE[42883]: Undefined function: 7 ERROR: function date_format(date, unknown) does not exist

can somebody help me with this.

Answer 1

This can be simplified. Just generate the proper datetime format (without minutes) and use DATE_TRUNC() to compare it:

$dateNowToMinute = Carbon::now()->format('Y-m-d H:i');
$recordings = Recording::with('therapy')
    ->where(DB::raw("DATE_TRUNC('minute', executes_at)"), '=', $dateNowToMinute)
    ->get();