Convert date in date list condition to list of date ranges

Question

I want to look for all the records that occur on specific dates.

SELECT *
FROM table1
WHERE date(column) in ($date1, $date2, ...);

However, as many of you, know this kind of comparison doesn't get along with indexes. So, I was wondering if there is a simple way to convert this query to something of the style of the following query without too much effort (i.e., : not using an external tool).

SELECT *
FROM table1
WHERE (column >= $date1 AND column < $date1 + interval 1 day)
   OR (column >= $date2 AND column < $date2 + interval 1 day)
   ...

So the optimizer can still use the indexes. (I'm using MySQL, but ANSI SQL would be great)

RolandoMySQLDBA · Accepted Answer · 2015-05-10T10:22:17.847

SUGGESTION #1

SELECT A.* FROM table1 A INNER JOIN
(
    SELECT '2015-03-01' dtcolumn
    UNION SELECT '2015-03-15'
    UNION SELECT '2015-04-01'
    UNION SELECT '2015-04-15'
    UNION SELECT '2015-05-01'
) B ON
A.dtcolumn >= B.dtcolumn
AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

SUGGESTION #2

SELECT * FROM table1 WHERE
(column >= '2015-03-01' AND
column < '2015-03-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-03-15' AND
column < '2015-03-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-01' AND
column < '2015-04-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-15' AND
column < '2015-04-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-05-01' AND
column < '2015-05-01' + INTERVAL 1 DAY);

Ditto · Answer 2 · 2015-05-04T18:14:55.767

Try something like this: (I did this on Oracle, it should mostly work elsewhere, the WITH clauses are mostly for just faking sample data .. so not entirely necessary)

  with w_date_list as (  -- just some sample input dates - these are from your IN list (note that you want to re-org them as a "table" not an IN list - there's ways of doing that if you need help with that step
        select to_date('01-apr-2015','dd-mon-yyyy') cdate from dual union all
        select to_date('02-apr-2015','dd-mon-yyyy') cdate from dual union all
        select to_date('03-apr-2015','dd-mon-yyyy') cdate from dual union all
        select to_date('04-apr-2015','dd-mon-yyyy') cdate from dual union all
        select to_date('05-apr-2015','dd-mon-yyyy') cdate from dual
        ),
        w_date_rng as (      -- re-organize them into ranges using LEAD analytic function
           select cdate start_date, 
                  nvl(lead(cdate) over (order by cdate), cdate + 1 )  end_date  -- last one, just default to 1 day
             from w_date_list
           )
  select *
    from (select to_date('01-jan-2015 03:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('01-mar-2015 03:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('01-apr-2015 03:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('01-apr-2015 10:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('01-apr-2015 13:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('02-apr-2015 03:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('02-apr-2015 21:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('04-apr-2015 03:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('04-apr-2015 15:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('04-apr-2015 15:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('04-apr-2015 15:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('05-apr-2015 08:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual union all
          select to_date('05-apr-2015 16:14:46','dd-mon-yyyy hh24:mi:ss') yourdate from dual
           ) table1   ,  -- this just some fake data for your "table1" table.
         w_date_rng   wd
   where table1.yourdate between wd.start_date and wd.end_date  -- join the two with range ... it'll use an index on "yourdate" if it exists
  /

Results:

  YOURDATE             START_DATE           END_DATE
  -------------------- -------------------- --------------------
  01-apr-2015 03:14:46 01-apr-2015 00:00:00 02-apr-2015 00:00:00
  01-apr-2015 10:14:46 01-apr-2015 00:00:00 02-apr-2015 00:00:00
  01-apr-2015 13:14:46 01-apr-2015 00:00:00 02-apr-2015 00:00:00
  02-apr-2015 03:14:46 02-apr-2015 00:00:00 03-apr-2015 00:00:00
  02-apr-2015 21:14:46 02-apr-2015 00:00:00 03-apr-2015 00:00:00
  04-apr-2015 03:14:46 04-apr-2015 00:00:00 05-apr-2015 00:00:00
  04-apr-2015 15:14:46 04-apr-2015 00:00:00 05-apr-2015 00:00:00
  04-apr-2015 15:14:46 04-apr-2015 00:00:00 05-apr-2015 00:00:00
  04-apr-2015 15:14:46 04-apr-2015 00:00:00 05-apr-2015 00:00:00
  05-apr-2015 08:14:46 05-apr-2015 00:00:00 06-apr-2015 00:00:00
  05-apr-2015 16:14:46 05-apr-2015 00:00:00 06-apr-2015 00:00:00

  11 rows selected.

This could be "generalized" as:

select *
 from table1   ,
      ( select cdate start_date, 
               nvl(lead(cdate) over (order by cdate), cdate + 1 )  end_date 
          from (  select to_date('01-apr-2015','dd-mon-yyyy') cdate from dual union all
                  select to_date('02-apr-2015','dd-mon-yyyy') cdate from dual union all
                  select to_date('03-apr-2015','dd-mon-yyyy') cdate from dual union all
                  select to_date('04-apr-2015','dd-mon-yyyy') cdate from dual union all
                  select to_date('05-apr-2015','dd-mon-yyyy') cdate from dual
                  )  w_date_list
        )   wd
where table1.yourdate between wd.start_date and wd.end_date  
/

That should work on any DB ... MYSQL, Oracle, whatever.

just need to input the date list range - it would be best to feed it in as another table or such ...

I could show how to do it Oracle but probably not terribly useful to you :) Might need another question for that if needed.

score 0 · Answer 3 · answered May 10 '15 at 10:16

I'm agree with second suggestion of RolandoMySQLDBA answer that using UNION instead of OR makes the seeking of the results too faster, And also we know:

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

And as your requirement I think your dates are in a separate sequence that will not make any duplicate row, So, for avoiding that duplicate-row removal that contains a hidden order by and etc. within it, I suggest you to use UNION ALL instead of UNION, like this:

SELECT * FROM table1 WHERE (column >= $date1 AND column < $date1 + interval 1 day)
UNION ALL
SELECT * FROM table1 WHERE (column >= $date2 AND column < $date2 + interval 1 day)
UNION ALL
...

Convert date in date list condition to list of date ranges

3 Answers3

SUGGESTION #1

SUGGESTION #2