Winston{yw}

For my current project, a small part of it requires that a web form be built that would request for a date range input from the user. On submit, the form would send a select statement, together with the date range input as a condition, to the underlying database, retrieve a set of results and display it. For this purpose, naturally I am using the BETWEEN .. AND function as discussed previously.

Recall that if you would like to return results for the month of March, you would need to have a condition like:

WHERE created_at BETWEEN "2008-03-01" AND "2008-04-01"

in which created_at is a datetime value.

However, the date range “2008-03-01″ to “2008-04-01″ is not a practical input to the web form. Intuitively, if this date range were to be entered as a form input, the user would have intended that the form returned all results between March 1st to April 1st inclusive. Instead, to return only the results between March 1st and March 31st, a more intuitive input would have been “2008-03-01″ to “2008-03-31″.

Then how can we allow the user to key in a set of intuitive inputs (but erroneous when fed directly to the database), and at the same time return a set of correct results? To overcome this, we have two solutions:

First Solution

Programatically, increase the latter date by a day before constructing your WHERE clause in your select query. For example, we modify “2008-03-31″ to become “2008-04-01″ before issuing the select query to the database.

However, this is not an elegant solution as extraneous code is required to massage the input in order to yield the correct set of results.

Second (and Better) Solution

Use the DATE() function to extract the date part of the datetime value, created_at, in the WHERE conditional clause:

WHERE DATE(created_at) BETWEEN "2008-03-01" AND "2008-03-31"

In this case, you are effectively only comparing the date part of the created_at value to the date range, and timestamp is completely omitted from the comparison.

Simple, intuitive, and yields the expected set of results.

Lately, I have been very much involved in the developmental aspects of MySQL, and searching by date or timestamp on a very large set of data is by far the most common task that I am required to do. Due to this, I was able to refresh my knowledge on MySQL and able to pick up some tips and tricks on working with date or timestamp in MySQL.

One of the helpful functions I found, was BETWEEN..AND… Previously, if I was extracting data between two dates, e.g. all records that occurred in the month of January, I would code my query like this:

select * from table_name
where created_at >= "2008-01-01" and created_at <= "2008-02-01;

However, the “greater than” and “lesser than” comparison can actually be replaced by a single function, BETWEEN..AND..:

select * from table_name
where created_at between "2008-01-01" and "2008-02-01;

Do note that the first date to be specified in the query MUST be earlier than the second date, else the query would simply return an empty set. Hope you won’t repeat the same mistake as I did.. I had to spend like 15 minutes looking at the query before I discovered the gruesome error.

In addition, if you look at the documentation, it states that:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.

Why then did I use “between ‘2008-01-01′ and ‘2008-02-01′” to extract all information that occurred within the month of January, and not “between ‘2008-01-01′ and ‘2008-01-31′”?

This is because, if the timestamp is not stated (i.e. only a date is used), MySQL automatically assumes a timestamp of 00:00:00 for the date. Hence, if I had used “between ‘2008-01-01′ and ‘2008-01-31′”, MySQL would have changed that to “between ‘2008-01-01 00:00:00′ and ‘2008-01-31 00:00:00′”, which clearly indicates that any record with a date of 2008-01-31 and timestamp greater than 00:00:00 would be excluded from the query. This is different from what we want, which is all the records in the month of January, 31st January inclusive.

Try this to verify that MySQL sets a timestamp of 00:00:00 for a date:

mysql> select current_date();

+----------------+
| current_date() |
+----------------+
| 2008-03-17     |
+----------------+
1 row in set (0.00 sec)

mysql> select timestamp(current_date());

+---------------------------+
| timestamp(current_date()) |
+---------------------------+
| 2008-03-17 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)

Hopefully, this post has helped you to better understand how to use BETWEEN..AND.. to query your databases. In my subsequent posts, I will look at other useful MySQL functions.