Using MySql between a sentence with dates

Question

I always had a problem with this, in order to clarify the goals when using mysql between a sentence, whether it includes parameters or only the values ​​that fall between them, for example:

where date between '2013-06-01' and '2013-06-06' 

whether this operator above will include values ​​with the date 2013-06-01 or only with "2013-06-02", and what happens if the instruction remains as it is, but then the date values ​​have hours in them, MySql will automatically clock for that approval

Answer 1

Fabio is actually wrong if hours, minutes, and seconds are included at this time.

 where date >= '2013-06-01' and date <= '2013-06-06' 

becomes internally

 where date >= '2013-06-01 00:00:00' and date <= '2013-06-06 00:00:00' 

So, you really just select 1 second from 2013-06-06, not all day!

Same thing BETWEEN, of course. To get all day 2013-06-06 you have to write

 where date >= '2013-06-01' and date <= '2013-06-06 23:59:59' 

or

 where date BETWEEN '2013-06-01' AND '2013-06-06 23:59:59' 

Go ahead, try it yourself (or see that it lives in sqlfiddle ):

 create table foo (my_date date, my_timestamp timestamp, my_datetime datetime); insert into foo values ('2013-06-06', '2013-06-06 12:23:34', '2013-06-06 13:35:48'); select * from foo where my_date <= '2013-06-06'; /*returns row*/ select * from foo where my_timestamp <= '2013-06-06'; /*does NOT return row*/ select * from foo where my_datetime <= '2013-06-06'; /*does NOT return row*/ select * from foo where my_timestamp <= '2013-06-06 23:59:59'; /*returns row*/ select * from foo where my_datetime <= '2013-06-06 23:59:59'; /*returns row*/ 

Answer 2

I suppose your column is in DATETIME format. This will include both the actual start time and the end time. It is interpreted by mysql since it

 where date >= '2013-06-01' and date <= '2013-06-06' 

If hours, minutes and seconds are included, it will act in exactly the same way as in the example.

Answer 3

The documentation at http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_between indicates that the range is included:

If expr is greater than or equal to min, and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all arguments are of the same type.