Laravel get highest value of day in database-CodePudding

I would like to get the day with the highest value from my database.

My database structure looks like this:

id | value | date
1    5       2022-02-07 10:00:00
2    9       2022-02-07 11:00:00
3    2       2022-02-07 12:00:00
4    1       2022-02-08 02:00:00
5    2       2022-02-08 09:00:00
6    5       2022-02-09 13:00:00
7    8       2022-02-09 18:00:00

What I want to get out:

[
   'value' => 16,
   'date' => 2022-02-07
]

Normally I would show you some some ideas, but I really have no idea how to do it. If there are millions of entries, I can't just pull the entire query and loop through it.

Does anyone have an idea? Thanks very well!

CodePudding user response：

I think we could use:

SELECT SUM(value) AS sum_val, DATE(date) AS date_group
FROM mytable
GROUP BY DATE(date)
ORDER BY sum_val DESC
LIMIT 1;

May be you could need instead:

DATE(FROM_UNIXTIME(MyTimestamp))

CodePudding user response：

Based on Ansev´s answer, here the Laravel Eloquent version:

DB::table('mytable')
    ->selectRaw('SUM(`value`) AS sum_val, DATE(`date`) AS date_group')
    ->groupBy('date_group')
    ->orderByDesc('sum_val')
    ->limit(1)
    ->get();