Laravel Tips: Time between us

Examples of using SQL between and datetime in Laravel

Laravel Tips: Time between us

If you've ever worked with a database, you know how useful it can be to be able to search for specific data based on certain criteria, Most importantly keep track of important dates and times. In this article, I will share two code snippets that can make your life easier while working with filtering dates and times.

The first scenario is to get data between two dates. That's where the whereBetween() method in Laravel comes in handy.

$start_datetime = now()->subDays(1); //i.e. 2023-01-01 11:51:08
$end_datetime = now()->addDays(30); // 2023-01-30 11:51:08

Campaign::whereBetween(
    'starts_at',
    [$start_datetime, $end_datetime]
)->get();

Here, we're using the whereBetween() method to search for all records that fall within a certain date range. The first argument, 'starts_at', is the name of the column to search in. The second argument is an array of two values that represent the start and end of the range we want to search for. So, we're searching for campaigns that start at some point between the $start_datetime and $end_datetime values.

The whereBetween() method is useful to easily search for records that fall within a certain range and this code snippet can help you quickly find and analyze data within a specified time frame.

Now, in reality, we come across scenarios where we need to perform more complex queries than just simple where clauses or other predefined Elequent queries. Such as, instead of our previous scenario, If we want to get the active campaigns for a specific date and time, then we can not use whereBetween(), as we want to search for a value between two columns. For this, we have to write a custom SQL query and Laravel provides, whereRaw() method that allows you to write SQL code directly.

Here's an example of the given scenario:

$query_datatime = '2023-01-25 11:51:08';

Campaign::whereRaw('? between starts_at and ends_at', [$query_datatime])->get();

In this case, we're searching for records where a specific datetime value falls between the starts_at and ends_at columns.

The whereRaw() method takes two arguments:

  1. The raw SQL code to execute (in this case, '? between starts_at and ends_at')

  2. An array of values to substitute into the SQL code (in example, [$query_datatime])

The ? in the SQL code is a placeholder for the value that we're passing in through the array. This helps prevent SQL injection attacks. So, essentially, this code is searching for all campaigns that are active at the date and time specified in $query_datatime.

Sometimes, whereRaw() can make your code harder to read and maintain, but it's useful when you need to use advanced SQL functions or operators that aren't supported by Laravel's query builder. Normally, the above code is used frequently by me in various filtering and searching features. So, I use Laravel's Query Scope or Custom Query Builder over this to keep it clean and reusable.