CODEWITHSUNDEEP
mysqlsql
neezer
neezer

Reputation: 20570

MySQL: IN clause for dates BETWEEN a start and end date?

I have list of dates in the format YYYY-MM, and I have a table full of records with a starts_at and ends_at column.

I want to find all records for which any given date in the passed-in list is within the starts_at and ends_at range.

So, given 2011-01, 2011-05, 2011-10, I want:

| id | starts_at           | ends_at             | title                             |
| 3  | 2010-12-05 00:00:00 | 2011-02-02 00:00:00 | something cool                    |
| 4  | 2011-03-14 00:00:00 | 2011-05-01 00:00:00 | something else really cool        |
| 5  | 2011-10-31 00:00:00 | 2012-12-23 00:00:00 | argh! end of the world! not cool! |

... while these records would be omitted:

| id | starts_at           | ends_at             | title                             |
| 6  | 2010-10-05 00:00:00 | 2010-12-02 00:00:00 | something uncool                  |
| 7  | 2011-03-14 00:00:00 | 2011-04-31 00:00:00 | something else really uncool      |
| 8  | 2011-12-23 00:00:00 | 2013-01-01 00:00:00 | yay! we're still alive! cool!     |

How would I write this WHERE condition in SQL?

Clarification: I'm looking for a solution in pure SQL (I'm working with a stored procedure, so dynamic injection through some other language like PHP isn't really possible, so far as I know), and the list of dates is being passed in to the query as a string (HTML form input). I would love to break it down into sequential BETWEEN statements if I could do that programmatically in SQL, but I've no clue how to do that.

Basically, I need a way to express the following logic in pure SQL:

$months = explode(',', '2011-01,2011-05,2011-10');
$q = "SELECT records.* FROM records WHERE";
foreach($months as $month) {
  $q .= " '$month' BETWEEN records.starts_at AND records.ends_at OR";
}
$q = substr($q, 0, -3) . ';';

Upvotes: 3

Views: 2955

Answers (2)

Mark Byers
Mark Byers

Reputation: 838696

SELECT id, starts_at, ends_at, title
FROM yourtable
WHERE '2011-01-01' BETWEEN starts_at AND ends_at
OR    '2011-05-01' BETWEEN starts_at AND ends_at
OR    '2011-10-01' BETWEEN starts_at AND ends_at

Upvotes: 2

pilcrow
pilcrow

Reputation: 58651

You can translate your month specifiers into start of month and end of month (som and eom below) and do it yourself:

SET sql_mode='ansi';

SELECT neezer.*
  FROM neezer
  JOIN (SELECT m || '-01' AS som,
               DATE_SUB(DATE_ADD(m || '-01', INTERVAL 1 MONTH), INTERVAL 1 SECOND) AS eom
          FROM (SELECT '2011-01' AS m
                 UNION ALL
                SELECT '2011-05'
                 UNION ALL
                SELECT '2011-10') d) month_intervals
       ON som <= ends_at AND eom >= starts_at;

So, we convert each '2011-01' into a paired DATE and DATETIME, and then compute the overlap. (The reason we convert eom into 'YYYY-MM-DD 23:59:59' is so that '2011-01' matches an interval that starts_at '2011-01-01 01:00:00' (otherwise eom would be coerced to 00:00:00 on the initial day and not match our starts_at).

Upvotes: 0

Related Questions