Querying table with a date range


Problem

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:

id name date_created
1 bob 2017-01-05 08:40:00
2 jeff 2016-01-10 12:10:10

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 00:00:00.

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?



Solution

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.

Comments




Please sign-in to post a comment