Random Coding

Photo by Unknown

Story of a MySQL DateTime Comparison Gotcha

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:

<pre class="wp-block-preformatted">SELECT something
FROM the_table
WHERE some = 'condition'

Would become Laravel Eloquent:

<pre class="wp-block-preformatted">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.

![Image with obscured text showing a diff of financial data from old and new system. ](https://randomcoding.com/wp-content/uploads/2021/09/blog.png)
Each yellow line represents a row where the data differs between the new and old subscription systems
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

<pre class="wp-block-preformatted">SELECT account, service
FROM customer_accounts
WHERE 
   `start` <= now() 
   AND `end` >= now()

Converted to Eloquent

<pre class="wp-block-preformatted">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 start and end columns are date columns.

<pre class="wp-block-preformatted" id="block-9d577cde-60a2-4fe0-83b1-0e521693bb6f">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)

<pre class="wp-block-preformatted" id="block-726dbc16-705e-46a1-85fb-09c6e1d50f15">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.

![](https://randomcoding.com/wp-content/uploads/2021/09/image.png)
Sample data set with some dates
And now some queries to query the data, click the image below to see the larger version.
[![SQL query results of a table that is queried with implied time values and explicit time values. ](https://randomcoding.com/wp-content/uploads/2021/09/image-1-1024x260.png)](https://randomcoding.com/wp-content/uploads/2021/09/image-1.png)
The different results based on whether you’re evaluating a Date or DateTime column
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:

<pre class="wp-block-preformatted" id="block-deb67585-cd72-454c-8196-e23b9ae8710a">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.