7

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)

| improve this question | |
  • Why do you think your proposed approach will "get along with indexes" better? – mustaccio May 4 '15 at 17:20
  • 1
    Because indexes break when you apply date function on timestamp or datetime fields. – msemelman May 5 '15 at 16:47
  • In the first query of your question you use the date() function (apparently to convert column to the date data type), while in the second query you don't, and then you say that the use of date() "breaks indexes". So why do you use it in the first place, if (as follows from the second query) it is not necessary? – mustaccio May 6 '15 at 18:32
7
+50

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);
| improve this answer | |
  • I prefer Suggestion #1. I think it's more clear. – msemelman May 4 '15 at 17:28
  • And more "maintaneable" if you can call this a mainteneable query. – msemelman May 4 '15 at 17:29
1

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.

| improve this answer | |
  • Thank you @ypercube for noting this answer is not broad enough. And just to note, mysql does not support "with" statement (even though it's SQL-99 stackoverflow.com/questions/324935/mysql-with-clause) so it would be neccesary to use a subselect. – msemelman May 4 '15 at 17:15
  • Well, I used the WITH statement mostly as a place holder for data, but sure, I'll update that .. ;) – Ditto May 4 '15 at 18:08
  • My comment was not (only) about with. nvl() and to_date() are proprietary Oracle functions. with and lead() and over are standard SQL but not implemented in MySQL. This answer might be fine for Oracle but needs substantial changes and effort to make it work in MySQL. Only dual would work as it is (although it's another non-standard feature.) – ypercubeᵀᴹ May 10 '15 at 13:26
  • Apart from that, the between is just wrong. The OP has correctly used >= and < in the question, there is no reason to change it. And I don't see why we need to use lead() anyway, It seems to change what the OP wants to something else. – ypercubeᵀᴹ May 10 '15 at 13:26
0

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
...
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.