Laravel Group data by Month Query works with MySQL but Fails on PostgreSQL database

Question

I'm trying to group data by month for the current year. The query below works on MySQL database but gives an error when connected to PostgreSQL.

 $results = DB::table('visits')
        ->select(DB::raw('month(visitdate) month, count(*) data'))
        ->where('team_id', 2)
        ->whereYear('visitdate', now()->year)
        ->groupBy('month')
        ->get();

This error was generated:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "month" LINE 1: select month(visitdate) month, count() data from "visits" w... ^ (SQL: select month(visitdate) month, count() data from "visits" where "team_id" = 2 and extract(year from "visitdate") = 2021 group by "month")

I tried a similar query with Eloquent and got the same results.

The visitdate column stores the date in the format "2021-01-01" I'm using laravel 8 with Breeze

jjanes · Accepted Answer · 2021-02-28 16:21:48Z

0

month is a keyword. You should choose something else instead, or put it in double quotes, or in this case just put AS in front of it. Any of the three should work.

answered Feb 28, 2021 at 16:21

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel Group data by Month Query works with MySQL but Fails on PostgreSQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related