Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Better Way to Handle Dates in Where Clause

Tags:

date

mysql

I was wondering which type of query is better to use:

SELECT * FROM table WHERE DATE >= '2010-7-20' AND DATE <= '2010-7-24'

OR

SELECT * FROM table WHERE DATE_SUB('2010-07-24',INTERVAL 4 DAY) <= DATE
like image 431
brant Avatar asked Dec 06 '22 02:12

brant


2 Answers

I always use the >= and < combination.

So if you want to serch for a those four days you would use

where DATE >= '2010-07-20' AND DATE < '2010-07-24'

This ensures that you only get dates your interesrted in even if there's a mixtrure of acutal dates(with no time component ) and datetimes.

So if you had a date stored as 2010-07-20 09:00:00 and a date stored as 2010-07-20 they would both be included, but the date 2010-07-24 wouln't.

I don't trust Between! I'm not sure about MySQL but BETWEEN in SQL Server would give you inclusive results so a row with a date of 2010-07-24 would be included when I wouldn't expect it to be if you wanted the four date period from the 20th? (20th, 21st, 22nd & 23rd)

So ANY date on the 24th shouldn't be included even if it was midnight..!

Also... Typically using functions in your criteria can prevent the optimizer using any indexes..! Something to be aware of.

More Importantly... The second query doesn't produce the same results as the first query...Is it all there?

like image 70
Dog Ears Avatar answered Dec 10 '22 11:12

Dog Ears


use between?

SELECT * FROM table WHERE DATE BETWEEN '2010-7-20' AND '2010-7-24'

like image 41
Marcx Avatar answered Dec 10 '22 10:12

Marcx