how to get unique year and month list from database "date" column

here is my code. its working fine.

SubjectReport::selectRaw('YEAR(date) as year, MONTH(date) as month')->distinct()->get();

I want to know is it another way to get data from database using eloquent or collection methods?

example:

2023-01
2023-02
2023-03

thanks.

Tray2

2 years ago

Level 73

You can use TO_CHAR

SELECT DISTINCT TO_CHAR(date, 'YYYY-MM') FROM table;

https://mariadb.com/kb/en/to_char/

Snapey

2 years ago

Level 122

you just want a list of dates for which there is data?

tisuchi

2 years ago

Level 70

@mahbubrn Might not an efficient way, but you may get the result.


$uniqueDates = SubjectReport::selectRaw('YEAR(date) as year, MONTH(date) as month')
    ->groupBy('year', 'month')
    ->get()
    ->map(function ($item) {
        return $item->year . '-' . str_pad($item->month, 2, '0', STR_PAD_LEFT);
    })
    ->toArray();

Please or to participate in this conversation.