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

Question

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) . ';';

Mark Byers · Answer 1 · 2011-11-05T18:46:31.793

2

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

edited Nov 05 '11 at 18:46

answered Nov 04 '11 at 20:01

Mark Byers

811,555
193
1,581
1,452

Not quite what I was after... the list of dates is a string passed in to the query, not as individual variables... something like this: `WHERE record.an_imaginary_date_field IN (2011-01, 2011-05, 2011-10)`... unless there's a way in SQL to parse out that string to get separate values? – neezer Nov 04 '11 at 20:05
@neezer: It's probably best to parse it to separate values at the client. – Mark Byers Nov 04 '11 at 20:06
@MarkByers There's no SQL-only way to accomplish this? I'm working with a stored procedure here that's rather complex. There's no way to pass an array of months to the stored procedure in place of the imploded string, then loop over that array to do the `BETWEEN` statements listed above? – neezer Nov 04 '11 at 21:32
@MarkByers, did you try this? For me this only works if `starts_at` and `ends_at` are strings. If they are instead DATETIME or TIMESTAMP columns, MySQL will NULLify the string _YYYY-MM_ value (ER_TRUNCATED_WRONG_VALUE), and so the BETWEEN will always be false. – pilcrow Nov 05 '11 at 01:10
@pilcrow: He said they were strings in his question. – Mark Byers Nov 05 '11 at 09:04
@MarkByers, I only see that the OP said that the search criteria were passed in as strings, not the columns. – pilcrow Nov 05 '11 at 12:29
@neezer: You will either need to split the string and join, or use dynamic SQL to create the query. – Mark Byers Nov 05 '11 at 18:47

pilcrow · Answer 2 · 2011-11-04T21:19:38.807

0

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).

edited Nov 04 '11 at 21:19

answered Nov 04 '11 at 20:26

pilcrow

56,591
13
94
135

Hmm, is there any reason why you don't use `BETWEEN` here? Also, is there anyway to do this programmatically in pure SQL? See my edit above for what I mean... – neezer Nov 04 '11 at 23:36
@neezer, I'm assuming `starts_at` and `ends_at` are temporal types, not strings. See my comment on MarkByer's answer. As to your "pure SQL" question, no. However, a stored procedure of your own design might be able to split the string and spit out the right query or populate a temporary table with the date ranges you care about. – pilcrow Nov 05 '11 at 01:14

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

2 Answers2

Linked