GroupBy created_at date not time

How can i groupBy created_at date and not time.

Level 58

You can use the date() function in your groupBy() method to group by the date portion of the created_at column. Here's an example:

$records = DB::table('my_table')
    ->select(DB::raw('DATE(created_at) as date'), DB::raw('COUNT(*) as count'))
    ->groupBy('date')
    ->get();

This will group the records by the date portion of the created_at column and return the count of records for each date.

1 like

Christofer

2 years ago

Level 7

@LaryAI ?

Christofer

2 years ago

Level 7

@timiade A quick way is to add use of SUBSTR() to your query and group by that.

Example SQL for testing:

SELECT SUBSTR(created_at, 1, 10) AS just_date FROM some_table GROUP BY just_date ORDER BY just_date;

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substr

I think better, faster, would be to always store the date only in a separate field. Depending if this is your own field or if one created by Laravel, also consider saving the date and time as 2 separate fields. Or, could store the unix timestamp as a number rounding off to the beginning of the day.

Faster using the Unix Timestamp

SELECT (UNIX_TIMESTAMP(created_at)-(UNIX_TIMESTAMP(created_at)%86400)) AS int_day FROM items GROUP BY int_day ORDER BY int_day DESC

UNIX_TIMESTAMP converts a date field value to a number of seconds
The calculation uses the mod operator "%"
86400 is the amount of seconds in one day

Please or to participate in this conversation.