Custom date format when using groupBy

Which database are you using?

I will assume it is MySQL. Try changing the ->groupBy(...) by one of these:

->groupByRaw('CAST(recordtime AS DATETIME)')

or

->groupByRaw('CAST(recordtime AS DATE)') // if you want to group only by date

or

->groupByRaw("DATE_FORMAT(recordtime, '%Y-%m-%d %H:%i:%s')")

For each case you'll also need to change the first selectRaw to match the group by, for example if you chose the last option:

$students = MeasCanal::whereBetween('recordtime', $dateScope)
  ->selectRaw("DATE_FORMAT(recordtime, '%Y-%m-%d %H:%i:%s') AS recordtime")
  ->selectRaw('max(formattedvalue) filter (where fullname = \'Данни.Кота\') as kota')
  ->where(function ($query) {
    $query->where('fullname', 'like', "Данни.Кота")
          ->orWhere('fullname', 'like', "Данни.Напрежение")
  ->groupByRaw("DATE_FORMAT(recordtime, '%Y-%m-%d %H:%i:%s')")
  ->orderBy('recordtime')
  ->get();
    return response()->json($students);
  }

Reference on DATE_FORMAT: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

As you are using the filter with the max aggregate it might not be MySQL after all. The CAST options should work fine for any DB you might be using:

$students = MeasCanal::whereBetween('recordtime', $dateScope)
  ->groupByRaw('CAST(recordtime AS DATETIME) AS recordtime')
  ->selectRaw('max(formattedvalue) filter (where fullname = \'Данни.Кота\') as kota')
  ->where(function ($query) {
    $query->where('fullname', 'like', "Данни.Кота")
          ->orWhere('fullname', 'like', "Данни.Напрежение")
  ->groupByRaw('CAST(recordtime AS DATETIME)')
  ->orderBy('recordtime')
  ->get();
    return response()->json($students);
  }

Thax for the answer. I'm using postgresql. The last code you provided have error

"Call to undefined method Illuminate\Database\Eloquent\Builder::groupByRaw()"

btw instead:

->groupByRaw('CAST(recordtime AS DATETIME) AS recordtime')

Isnt right this

->selectRaw('CAST(recordtime AS DATETIME) AS recordtime')

Sorry, it was a copy and paste error (when I realized you are not using MySQL), also the where closure is not closed on your first example, so I overlooked that too.

Try this:

    $students = MeasCanal::whereBetween('recordtime', $dateScope)
        ->selectRaw('CAST(recordtime AS DATETIME) AS recordtime')
        ->selectRaw('max(formattedvalue) filter (where fullname = \'Данни.Кота\') as kota')
        ->where(function ($query) {
            $query->where('fullname', 'like', "Данни.Кота")
                ->orWhere('fullname', 'like', "Данни.Напрежение");
        })
        ->groupByRaw('CAST(recordtime AS DATETIME)')
        ->orderBy('recordtime')
        ->get();

    return response()->json($students);

Still I see the error mention in the previews reply.

groupByRaw was added in Laravel 6.16.0, released in 18/feb/2020.

https://github.com/laravel/framework/blob/7.x/CHANGELOG-6.x.md

If you are running Laravel 6, or 7, please run composer update

If you are in an older Laravel version try this:

->groupBy(DB::raw('CAST(recordtime AS DATETIME)'))

Don't forget to add the DB facade to your use clauses

Oh I see. I will check that. Now next error:

Undefined object: 7 ERROR:  type "datetime" does not exist

Is it related to version again?

No, datetime is a valid type in MySQL (and other DBMS too), don't know which is the equivalent in postgres.

From these lines:

https://github.com/laravel/framework/blob/7.x/src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php#L702-L713

I guess it should be something like this:

->groupBy(DB::raw('CAST(recordtime AS TIMESTAMP WITHOUT TIME ZONE)'))

But it might be wrong. Googling around this link came first:

https://www.blendo.co/documents/queries-casting-postgresql/

So you can also try this:

->groupBy(DB::raw('TO_TIMESTAMP(recordtime, 'YYYY-MM-DD HH24:MI:SS')'))

I don't use postgres so I don't know if it is correct. But it should be easy to find how to cast it in Google.

Don't forget to update the corresponding addSelect to match the expression