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.

Instead of `date_format` try using `to_char` E.g.: `to_char(now(), 'YYYY-MM-DD')`, Formatting Reference: https://www.postgresql.org/docs/8.4/functions-formatting.html — Akshay Khale, Oct 03 '19 at 08:12

GMB · Accepted Answer · 2019-10-03T08:18:11.873

2

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

edited Oct 03 '19 at 08:18

answered Oct 03 '19 at 08:07

GMB

216,147
25
84
135

Thank you, I just changed this ```'Y-m-d H:i'``` and it worked. – Emia Oct 03 '19 at 08:14

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

1 Answers1