The last few months of my employment have largely been spent moving big parts of our subscription functionality to one central location. This functionality used to be spread across three different systems, but we’re consolidating to make upgrades faster. Since some of this code had been written many many years ago (we’re turning 10 this year! but the code itself is between 8 and now years old), the code needed to go through some technology updates.
This raw SQL:
SELECT something FROM the_table WHERE some = 'condition'
Would become Laravel Eloquent:
DB::table('the_table') ->select(['something']) ->where('some', 'condition') ->get();
Fairly innocuous stuff on the surface. After many moons and lockdowns, the move was soon to be complete and the functionality ready to be tested against production data. This was the penultimate step, and the aim here is to run the financial data against both systems and end up with the same output.
At least, in theory. I set up the data and look at the diff. The left side of the image below highlights the differences between data from the old and new systems. On the right side are a bunch of commas and the MS Paint brush tool obscuring the actual raw data.
Clearly, something had gone awry in the porting. After a little digging and pairing down the data, I ended up at the queries below. The real queries are much larger but the included code has the pertinent bits.
The raw SQL
SELECT account, service FROM customer_accounts WHERE `start` <= now() AND `end` >= now()
Converted to Eloquent
DB::table('customer_accounts') ->select(['account', 'service']) ->whereDate('start', '<=', Carbon::now()) ->whereDate('end', '>=', Carbon::now()) ->get();
Looking at the generated query itself, we’re left with this. Eloquent seems to be converting into a date-only value. This sounds fine because both
end columns are date columns.
SELECT account, service FROM customer_accounts WHERE `start` <= '2021-09-24' AND `end` >= '2021-09-24'
However, here’s the gotcha. There’s an implied time component when you’re doing date comparisons. In the above query, it’s
00:00:00. And since we’re using
>= and <= and not
> and <, it enlarges the scope and also includes the date in the query (2021-09-24) within its boundary.
But if you look at the raw SQL query, we actually use MySQL’s NOW(). Which has an explicit time component when evaluated – as opposed to the Eloquent version which converts the Date Time value to only include the Date component. So the actual query running is something like (if you run it at 2.54 AM)
SELECT account, service FROM customer_accounts WHERE `start` <= '2021-09-24 02:54:00' AND `end` >= '2021-09-24 02:54:00'
MySQL will only evaluate 2021-09-24 as a date within the boundary if the time component is 00:00:00 because it’s a Date column. If it was a DateTime column, it would evaluate the time as it normally would. Eloquent wasn’t really doing anything wrong here, in fact, it was the correct behavior. The old code just had an evaluation that didn’t make sense, given the column type.
To illustrate the issue, have a look at this sample data set.
And now some queries to query the data, click the image below to see the larger version.
And now the difference in behavior should be pretty self-evident. So, what’s the takeaway here? The discrepancy is easily avoided if you just use the
> and < operators when working with the dates. But, since the goal is to keep the output identical, we needed to make sure the behavior is also identical.
The final solution ended up being something like this:
DB::table('customer_accounts') ->select(['account', 'service']) ->whereDate('start', '<=', DB::raw('NOW()')) ->whereDate('end', '>=', DB::raw('NOW()')) ->get();
And that fixed the issue, all I got was a sea of matching recordsets, a feast for my eyes.