MySQL. Checking date range and date column values with GREATEST, LEAST and IFNULL

Here’s a little problem I faced the other day. Filtering rows against a timescale based on three dates that may or may not exist. This way round the problem uses the IFNULL, GREATEST and LEAST MySQL functions. IFNULL has to be used in this scenario because of the issue in MySQL 5.0.13 which means that GREATEST or LEAST will return NULL if any of their arguments are NULL, in my opinion this makes the function more or less useless.

There are three date columns in the table, each can contain any date or be null. This query will return all records between a date range when at least one of the date columns has a value, taking the highest or lowest value date from the row.

If no dates exist in the row it will be filtered out by using the IFNULL function to create a date well outside the value of the argument (1900 or 2099).

1
2
3
4
5
6
7
8
SELECT date1, date2, date3 
FROM dates AS d
WHERE 
GREATEST(IFNULL(d.date1,MAKEDATE(2099,365)), IFNULL(d.date2,MAKEDATE(2099,365)), IFNULL(d.date3,MAKEDATE(2099,365))) <= '2013-06-03 16:08:46' 
AND  
LEAST(IFNULL(d.date1,MAKEDATE(1900,365)), IFNULL(d.date2,MAKEDATE(1900,365)), IFNULL(d.date3,MAKEDATE(1900,365))) >= '2008-04-01 16:08:46'

Update: 6 June 2013

The above doesn’t produce the results I wanted or expected. This query will return a result when all three of the columns sit within the known date range and all three are not null. This isn’t what I wanted. #FailColeman

What I actually needed was the following.

1
2
3
4
5
SET @t1='2008-04-01 16:08:46', @t2='2013-06-03 16:08:46';
SELECT date1, date2, date3 
FROM dates AS d
WHERE 
WHERE (date1 BETWEEN @t1 AND @t2)OR (date2 BETWEEN @t1 AND @t2)OR (date3 BETWEEN @t1 AND @t2)