Using an effective_date column in MySQL

There are times when I blog about things just because it's a nice place to share knowledge about how to get things done that aren't always obvious or straightforward. This is one of those things.

I have a table of "stuff" that uses an "effective date" to determine when a particular entry should be available. In this case, I want the largest effective date that's also not greater than the current moment. Sure - I could use a start and end date, but that would be a waste because it implies I know when I will stop using returned rows.

Let's assume I am showing users time-sensitive information from the following table (please ignore the lack of additional indexing):

CREATE TABLE user_notification (
   id             MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
 , category_id    MEDIUMINT UNSIGNED NOT NULL
 , effective_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
 , is_active      TINYINT(1) NOT NULL DEFAULT 1
 , message        MEDIUMTEXT NOT NULL
);

Next, let's assume that I only want to display the most up-to-date messages that are active for a given set of categories. There is one option that works for me no matter how many categories I use. It requires me to select and join from my user_notification table twice. When combining this data with other parts of a select, it will wind up being two joins.

  SELECT NOW() INTO @compare_date;
  SELECT un.*
    FROM user_notification
      AS un

    LEFT
    JOIN user_notification
      AS un2
      ON un2.id = un.id
     AND un2.effective_date > un.effective_date

   WHERE un.effective_date <= @compare_date
     AND un2.effective_date IS NULL

This might look a little strange, but here's how it works. Notice that the left join simply re-joins the same table back against the select, but, it's attempting to find the largest possible effective_date. The select's where clause limits us down to rows that are smaller than the compare date (NOW()).

This is one of those queries that may not come easily at first, but once you've done it a few times, you'll be able to crank out similar queries with ease.

Giving credit where credit is due - Thanks to my co-worker Brian P. for turning me on to this particular method of solving a similar problem.