MySQL - convert and compare dates on query

Question

I'm working on a football site witch, along with many features, list result tables. The tables are separated between seasons (season name, etc) and rounds (witch are generated everytime a new season starts up, like 2008/2009, 2009/2010, etc)

rounds table has a start date and a end date, and the only way I have to dinamically know wich round I should work with in the website frontend is by comparing those dates with the remaining rounds regarding the same season.

All the data is fetched from a xml, and the dates look like this :

2009-08-16

So, my 2 problems :

• compare dates (and probably convert them on-the-fly) and find out witch round has the biggest end date;
• get the right round (some future rounds may be already in the database but the current round could be another prior to those, if you know what I mean)

I'm not expert or anything with SQL, so I'd like to know how would you guys do that.

Here's my current query :

SELECT seasons.competition_id AS cid, 
       seasons.id AS sid, 
       rounds.id AS rid, 
       seasons.start_date AS sstart, 
       seasons.end_date AS send, 
       rounds.start_date AS rstart, 
       rounds.end_date AS rend, 
       round_groups.id AS rgid 
  FROM seasons 
INNER JOIN rounds ON seasons.id=rounds.season_id 
LEFT JOIN round_groups ON rounds.id=round_groups.round_id

ps: you may notice the round_groups table that I forgot to mention, it's used when a certain season is divided in groups, like UEFA and Champions.

EDIT :

A season is a entry on the database that is updated once per season, because some competitions change their name regarding the sponsor of the season. The round changes once per season at least, but may have more changes. In certain competitions like UEFA, they have some first elimination rounds, then a group phase, and then elimination again, wich would result in 3 different rounds. The final output should be a list of every round currently active regarding each season.

Answer 1

Let's assume the following table definitions (without proper indices or constraints)

CREATE TABLE seasons (
  id int auto_increment,  
  competition_id int,  
  start_date DATE,  
  end_date DATE, 
  description varchar(32),
  primary key(id)
)

CREATE TABLE rounds (
  id int auto_increment,  
  season_id int,  
  start_date DATE,  
  end_date DATE,    
  description varchar(32),
  primary key(id)
)

And some sample data

INSERT INTO seasons (competition_id,start_date,end_date,description) VALUES
  (1, '2007-3-15', '2007-9-15', 'FooCup 2007'),  
  (1, '2008-3-7', '2007-9-21', 'FooCup 2008'),  
  (1, '2009-4-1', '2007-9-21', 'FooCup 2009'),    
  (2, '2009-1-1', '2009-12-20', 'xyz 2009') 

INSERT INTO rounds (season_id, start_date, end_date, description) VALUES
  (1, '2007-3-15', '2007-4-15', 'foo 2007 1'),
  (1, '2007-4-16', '2007-8-15', 'foo 2007 2'),
  (1, '2007-8-16', '2007-9-15', 'foo 2007 3'),  
  (2, '2008-3-7', '2008-4-21', 'foo 2008 1'),
  (2, '2008-4-21', '2008-8-16', 'foo 2008 2'),
  (2, '2008-8-17', '2008-9-21', 'foo 2008 3'),
  (3, '2009-4-1', '2009-5-1', 'foo 2009 1'),
  (3, '2009-5-2', '2009-8-1', 'foo 2009 2'),
  (3, '2009-8-2', '2009-9-21', 'foo 2009 3'),
  (4, '2009-1-1', '2009-12-20', 'xyz ')

You a fix time/date (fd) and want the records having this fd between start_date and end_date. BETWEEN ... AND ... tests exactly that and works with date/time values. E.g.

SELECT
  id, description 
FROM
  round as r
WHERE
  '2008-6-17' BETWEEN  r.start_date AND r.end_date

returning the records for all rounds (for all competitions) at that time.
Now you JOIN this on the season_id with the seasons table. And limit the results to a certain competition_id (if you want).

SELECT
  r.description as rd, r.start_date, r.end_date    
FROM
  rounds as r  
JOIN
  seasons as s  
ON
  r.season_id=s.id
WHERE
  '2009-6-17' BETWEEN  r.start_date AND r.end_date  
  AND s.competition_id=1

edit: sorry, misread the question.... Assuming you know the competition_id and have a fix date (2009-08-16), you can get the season.id with

SELECT
  s.id  
FROM
  seasons as s  
WHERE
  s.competition_id=1
  AND '2009-08-16' BETWEEN s.start_date AND s.end_date

Now you can JOIN on season.id=rounds.season_id to get all rounds in this season.

SELECT
  r.id  
FROM
  seasons as s  
JOIN
  rounds as r  
ON
  s.id=r.season_id
WHERE
  s.competition_id=1
  AND '2009-08-16' BETWEEN s.start_date AND s.end_date

(rounds:id 7,8,9 in this case).
The simplest option to limit the result to the highest round.id is to let MySQL order rounds by rounds.id in descending order and limit the result to one record.

SELECT
  r.id  
FROM
  seasons as s  
JOIN
  rounds as r  
ON
  s.id=r.season_id
WHERE
  s.competition_id=1
  AND '2009-08-16' BETWEEN s.start_date AND s.end_date
ORDER BY
  r.id DESC
LIMIT
  1

use EXPLAIN SELECT ... to make sure the query isn't too inefficient...