Laravel 5.4 - Get one data from each duplicate data row

Question

I want to make select options to display the month taken from the created_at column of a table

// item_ins
+----+--------+---------------------+
| id |  item  |     created_at      |
+----+--------+---------------------+
|  1 | item 1 | 2017-12-11 10:37:52 |
|  2 | item 2 | 2017-12-11 10:38:17 |
|  3 | item 3 | 2018-01-12 01:28:43 |
|  4 | item 4 | 2018-01-12 01:30:14 |
|  5 | item 5 | 2018-02-12 01:30:05 |
|  6 | item 6 | 2018-02-12 01:30:42 |
+----+--------+---------------------+

in the table there are each two months december, january and february, and i want to get one from every same month, i try like this

$months= ItemIn::distinct('created_at')->pluck('created_at');

but I still get the same two months for each month because the timing is not the same, as below

and I'm still confused with how to get just a month because in the above way I get also years and days

[
    {
        "date": "2017-12-11 10:37:52.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2017-12-11 10:38:17.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-01-12 01:28:43.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-01-12 01:29:14.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-02-12 01:30:05.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-02-12 01:30:22.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
]

Answer 1

Use CAST with raw query to get your desire result like this,

$data = \DB::table('item_ins')
        ->select(\DB::raw('CAST(created_at as date) as created_at'))
        ->distinct('created_at')->get();

You can see docs here https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

I hope you will understand.

Answer 2

if the presence of time is not an issue, you can handle this at the time of using the select query itself, if you are selecting, by using substring.

select id, item, SUBSTRING(created_at, 1, 11) as created_at
from test
group by created_at;

Answer 3

Either format it in the view using Carbon

{{ \Carbon\Carbon::parse($user->from_date)->format('d/m/Y')}}

or use accessors in the model to reformat the date field

public function getCreatedAtAttribute($value) {
     return \Carbon\Carbon::parse($value)->format('m');
}

Then $model->created_at will be in desired format

Answer 4

Use groupBy():

$months = ItemIn::groupBy(DB::raw('MONTH(created_at)'))->selectRaw('DATE_FORMAT(created_at, "%M") as month')->get();