Searching Between Dates In MySQL

Sometimes, your application will require a search feature that returns rows that fall within a certain specific frame. For example: Viewing all blog posts that were published in March of 2014.

Note: When storing dates or timestamps, you should be using the DATE or DATETIME column types, especially if you intend on carrying out calculations on them. If you have made the mistake of using VARCHAR, then you are going to have to fix it before you progress.

The one thing that you need to know about time in general is that it gets bigger with each second. i.e. A date that will occur in the future will be larger than a date that has occurred in the past. If you look at the unix timestamp for the 25th of December, 2014, you’ll see that it is 1419465600. If you look at the timestamp for the 25th of December, 1987, you will see that the timestamp is 567388800. If you do a quick check, you’ll see that 1419465600 is a much larger number than 567388800. This is because 852076800 seconds have passed between the 25th of December, 1987 and the 25th of December, 2014. To summarise: Timestamps get bigger by the second.

Using this knowledge, we can easily pull back MySQL records that fall within a specific date range. For the purpose of this example, lets say that we want to find every news article that was posted between the 1st of October, 2014 and the 4th of October, 2014. The query will look like this:

1	SELECT title, date FROM news WHERE date >= '2014-10-01' AND date <= '2014-10-04';

The query above basically returns any row that has a date that is:

• Bigger than or equal to 2014-10-01.
• Lesser than or equal to 2014-10-04.

i.e. All news articles from the 1st, 2nd, 3rd and 4th.

An alternative way of selecting rows within a specific date range:

1	SELECT title, date FROM news WHERE (date BETWEEN '2014-10-01' AND '2014-10-04');

As you can see, there’s not much of a difference (they return the same results). Although the BETWEEN comparison operator does make the SQL query a bit more readable / human-friendly.

It is also worth noting that timestamps can also be used. For example, if I want to select rows that were inserted between 1pm and 2pm, I could run the following query:

1	SELECT title, date FROM news WHERE (date BETWEEN '2014-10-04 13:00:00' AND '2014-10-04 14:00:00');

This will return all news articles that were posted between 1PM and 2PM on the 4th of October, 2014.

Once you’ve wrapped your head around the fact that time gets bigger with each second, date-specific calculations get a little bit easier!