Winston{yw}

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.