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.
date()
function (apparently to convertcolumn
to thedate
data type), while in the second query you don't, and then you say that the use ofdate()
"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