Eloquent - Get records by day

Question

I have a list of database records that represent some events. Each has a start_time column that contains dates in this format: 2017-10-28 22:00:00. I would like to be able to get all the records whose column start_time is a Friday using Laravel. Something like this:

$fridayEvents = $q->whereDate('start_time', '=', isFriday());

But I am having a hard time creating isFriday() with Carbon.

Answer 1

MySQL (and other SQLs) implement the WEEKDAY() function that extracts the weekday from a date:

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
        -> 6
mysql> SELECT WEEKDAY('2007-11-06');
        -> 1

So you can do a query like this:

$q->whereRaw('WEEKDAY(your_table_name.start_date) = 4')

This way is more efficient then filtering results directly on PHP using Carbon:

• You will process data using native database functions that are faster then Carbon over PHP.
• Only the relevant data will travel from Database to PHP, reducing query time and memory usage

To get the top performance, you'll need to create a column to store the weekday, so your database will be able to use indexes to avoid full-table scan, giving you the best performance.