Querying data by a date field is fairly common, let's look at a basic query a lot of people write wrong when they want to fetch records by a date range.
The data we are going to play with looks like this:
We would like to find all records create from the 1st till the 5th of January. The first attempt might be this:
select * from date_table where date_created between '2017-01-01' and '2017-01-05'
Nothing is returned? Why? Our date value in the table also has a time 08:40 which is causing a problem as MySQL assumes a missing time as
Now here's what a lot of people might do to get around that problem, they simply remove the time portion like this:
select * from date_table where date(date_created) between '2017-01-01' and '2017-01-05'
This query produces the correct result, but it can be very slow. The reason for this is simple, since we are using a function on a table column, MySQL has to scan through all records, remove the
time from our
datetime column and then run the check.
What's a better way to query this table?
select * from date_table where date_created between '2017-01-01 00:00:00' and '2017-01-05 23:59:59'
We simply added the time to our query to make it a bit more precise, the field from the table stays untouched and can thus be queried by an index.