Filtering date data in MySQL using WHERE against a date

In a previous post I described a method of using DATE_FORMAT to sort data and search against it. That post described how you can sort your data and then use a WHERE clause against it. It basically described a 2 step process, this is alternative and does it in 1 step. There are benefits to both methods and I leave it up to you to decide which way to go.

N.B. I am sure this method is on a million different websites, I only include it on my blog so that it has both versions.

The SQL:

SELECT * FROM your_table WHERE your_timestamp > '2010-10-09 00:00:00' AND your_timestamp < '2010-10-09 23:59:59'

The Explanation:

It's quite simple really; MySQL performs a comparison where your value in your DATETIME field is greater than the minimum reference and less than the maximum. In the case above it selects all dates from a specific day (2010-10-09), but in theory you could do it on many different ranges.

I hope this helps.

Search functions against DATETIME in MySQL [using PHP]

When you are using a database for any large scale application, it is always a good idea to make a note of when things happen, i.e. when a page was created or when a page was updated. The easiest way of doing this is to pass NOW() through your SQL to your database to add the date and time to a DATETIME field, whereby you end up with data looking like this in your table:

2010-10-13 18:32:45

2010-10-13 18:28:01

2010-10-13 18:29:17

In its raw form, the SQL for performing this function will look something like this:

SELECT * FROM your_table WHERE DATE_FORMAT(column_name, '%Y-%m-%d')= 'your_date'

The SQL statement works in two stages:

1. MySQL first formats the data into your prescribed format, in my case, this was YEAR (%y), MONTH (%m), DAY (%d).
2. Now that MySQL has sorted the data internally, it performs the comparison part of the statement against your formatted data using the WHERE clause.

Of course, the internal functions that MySQL provides performs the vast majority of the hard work for you. The key to using this solution is to provide your string in the same format as the data is stored. In my case, the string I needed to run a comparison on was a date, the time wasn't a necessary part of the clause, so I provided:

'2010-10-13'

So my SQL looked like a little bit like this:

SELECT * FROM my_dates WHERE DATE_FORMAT(my_date_time, '%Y-%m-%d')= '2010-10-13'
(Notice the hyphen as the separator, this may differ in your case)

%Y = 4 digit numeric YEAR,
%m = 2 digit numeric MONTH,
%d = 2 digit numeric DAY

There are many other options for date formatting, all of which can be found here on the MySQL website:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

I have also written a different method of extracting data using a date, you can find that here.

I hope this helps...