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

Question

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

You mean that your return results should contain all the years and months of the given **Mjblog** model, if yes, then why are you doing this: `$post->where('created_at',$post)->get();`? — Mustafa Ehsan Alokozay, Sep 29 '15 at 11:50
My exact need is when I would click on archive month and year need to display the content of database table where there is created_at date field. — Pawan Dongol, Sep 30 '15 at 04:55

score 94 · Accepted Answer · answered Sep 29 '15 at 11:58

94

There are date helpers available in the query builder:

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

answered Sep 29 '15 at 11:58

Martin Bean

38,379
25
128
201

2

You can use `->whereMonth('created_at', $month)` also. Both will work. – Samir Mammadhasanov Jan 09 '19 at 11:38

M Khalid Junaid · Answer 2 · 2022-05-11T07:21:10.933

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

in simple question : Are you suggesting that this solution is way faster (in query time) then the accepted solutions? — Toni Tegar Sahidi, Mar 04 '21 at 00:08
@ToniTegarSahidi yes if `created_at` is indexed column this approach will utilize the added index the other one will neglect because year() and month() functions will be applied on query which will produce a new value other than indexed value — M Khalid Junaid, Mar 04 '21 at 07:33

score 9 · Answer 3 · answered Sep 29 '15 at 17:54

9

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.

answered Sep 29 '15 at 17:54

sebdesign

828
5
11

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

3 Answers3

Performance Review

Linked

Related