SQL: Difference between "BETWEEN" vs "current_date - number"

Question

I am wondering which of the following is the best way to implement and why.

select * from table1 where request_time between '01/18/2012' and '02/17/2012'

and

select * from table1 where request_time > current_date - 30

Answer 1

I ran the two queries through some of my date tables in my database and using EXPLAIN ANALYZE I found these results:

explain analyze
select * from capone.dim_date where date between '01/18/2012' and '02/17/2012'

Total runtime: 22.716 ms

 explain analyze
select * from capone.dim_date where  date > current_date - 30

Total runtime: 65.044 ms

So it looks like the 1st option is more optimal. Of course this is biased towards my DBMS but these are still the results I got.

The table has dates ranging from 1900 to 2099 so it is rather large, and not just some dinky little table.

Answer 2

Between has the inclusive ranges i.e when you issue a query like id between 2 and 10 the value of 2 and 10 will also be fetched.If you want to eliminate these values use > and <.

Also when indexes are applied say on date column > and < makes a good use of index than between.