CODEWITHSUNDEEP
phpsqllaraveleloquentlaravel-5.1
Pawan Dongol
Pawan Dongol

Reputation: 1158

How to select year and month from the created_at attributes of database table in laravel 5.1?

My problem is that I want to get data form the database table from the created_at attributes as per year and month only. The code I have tried is:

$post= Mjblog::select(DB::raw('YEAR(created_at) year, MONTH(created_at) month'));
$posts_by_y_m = $post->where('created_at',$post)->get();

Upvotes: 33

Views: 123328

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Performance Review

Although the accepted answer may solve the OP problem but that is NOT the OPTIMAL SOLUTION in terms of database performance. Because when whereYear() or whereMonth() helper is applied to query the records, it makes the query Non-SARGable. This means if the compared column created_at is indexed in database then this index is ignored while searching the data. See What makes a SQL statement sargable?

Consider following expression

$posts = Mjblog::whereYear('created_at', '=', $year)
               ->whereMonth('created_at', '=', $month)
               ->get();

The resultant query will be like

select * 
from mjblog 
where year(`created_at`) = :year 
  and month(`created_at`) = :month

The above query can be clearly seen as non-sargable because year() and month() functions are applied on created_at which produces a non index value.

To make it SARGable expression it's better to define the exact/range of values while comparing your indexed column. Like for OP the range can be derived from month and year values as

$year = 2000;
$month = 2;
$date = \Carbon\Carbon::parse($year."-".$month."-01"); // universal truth month's first day is 1
$start = $date->startOfMonth()->format('Y-m-d H:i:s'); // 2000-02-01 00:00:00
$end = $date->endOfMonth()->format('Y-m-d H:i:s'); // 2000-02-29 23:59:59

Now the SARGable expression can be written as

select * 
from mjblog 
where created_at between :start and :end

Or

select * 
from mjblog 
where created_at >= :start 
  and created_at <= :end

In query builder it can be represented as

$posts = Mjblog::whereBetween('created_at', [$start, $end])
               ->get();

Or

$posts = Mjblog::where('created_at', '>=', $start)
               ->where('created_at', '<=', $end)
               ->get();

Another helpful article that highlights the cons of Non-SARGable Predicates & Anti-Patterns

Upvotes: 14

sebdesign
sebdesign

Reputation: 838

If you want to get the year and month from a single instance of Mjblog you can access them like this:

$year = $post->created_at->year;
$month = $post->created_at->month;

Read more about Carbon\Carbon getters documentation.

Upvotes: 9

Martin Bean
Martin Bean

Reputation: 39389

There are date helpers available in the query builder:

$post = Mjblog::whereYear('created_at', '=', $year)
              ->whereMonth('created_at', '=', $month)
              ->get();

Upvotes: 100

Related Questions