CODEWITHSUNDEEP
mysql
suo
suo

Reputation: 609

Mysql group data biweekly

I'm currently using Laravel eloquent for my database logic implementation. I have data that needs to be grouped: weekly, bi-weekly and monthly.

Eloquent provides convenient methods for grouping data weekly and monthly

i.e for grouping data weekly, this works perfectly:

Model::select("my_colums"))
->groupBy('employee_id')
->groupBy(\DB::raw('WEEK(created_at)'))
->get();

Same case for grouping monthly, this works:

 Model::select("my_colums"))
->groupBy('employee_id')
->groupBy(\DB::raw('MONTH(created_at)'))
->get();

Have been trying unsuccessfully to follow a similar eloquent logic for grouping the data bi-weekly but I've not found any solution. Which got me questioning whether there is a pure Mysql implementation that would successfully group my data biweekly. If so, how would such a query be structured?

Upvotes: 0

Views: 324

Answers (1)

Akina
Akina

Reputation: 42727

->groupBy(\DB::raw('WEEK(created_at) DIV 2'))

or

->groupBy(\DB::raw('(WEEK(created_at) + 1) DIV 2'))

The idea is very simple. If we perform integer division by 2 then consecutive natural numbers will give consecutive pairs: 0 and 1 will give 0, 2 and 3 will give 1 and so on. WEEK() returns consecutive integers. If we'll group by this expression then each separate group will include 2 weeks.

Upvotes: 2

Related Questions