Return records that were created and completed within 4 hours

I have an Enrollment model with the following simplified migration:

$table->timestamp('completed_at')->nullable();
$table->timestamps();

The underlying database is Postgresql, and completed_at will be null until the user completes the course.

I want to write a query that will return all enrollments completed within 4 hours of being created. From reviewing the docs, the closest I've got is:

$enrollments = DB::table('enrollments')
->whereRaw('completed_at <= created_at + INTERVAL 4 HOUR')
->get();

$models = Enrollment::whereRaw('completed_at <= (created_at + INTERVAL \'4 hours\')')->get();

But I'm getting incorrect results for both.

LaryAI

3 years ago

Level 58

The issue with the current query is that it is checking if the completed_at timestamp is less than or equal to the created_at timestamp plus 4 hours. This means that it will return enrollments that were completed within 4 hours of being created, but it will also return enrollments that were completed after 4 hours of being created.

To fix this, we need to check if the completed_at timestamp is greater than or equal to the created_at timestamp plus 4 hours. This will ensure that we only return enrollments that were completed within 4 hours of being created.

Here's an example query that should work:

$enrollments = DB::table('enrollments')
    ->whereRaw('completed_at >= created_at + INTERVAL \'4 hours\'')
    ->whereRaw('completed_at IS NOT NULL')
    ->get();

This query uses the IS NOT NULL condition to ensure that we only return enrollments that have been completed. If the completed_at timestamp is null, it means that the enrollment has not been completed yet.

Note that the INTERVAL value needs to be wrapped in single quotes and escaped with a backslash, as shown in the example above.

tisuchi

3 years ago

Level 70

@colinlongworth I think this will bring the same result:

$enrollments = DB::table('enrollments')
    ->whereNotNull('completed_at')
    ->whereBetween('completed_at', [DB::raw('created_at'), DB::raw('created_at + interval \'4 hours\'')])
    ->get();

Please or to participate in this conversation.