在日期之间使用MySql子句 [英] Using MySql between clause with dates

问题描述

我一直对此有疑问，为澄清起见，当使用mysql between子句时，它是否包含参数或仅包含介于它们之间的值，例如:

I have always had a problem with this, for clarification purposes, when using mysql between clause, does it include the parameters or only the values that fall between them, for example:

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

上面的语句还将包括日期为2013-06-01或仅来自'2013-06-02'的值，如果语句保持原样，但日期值中包含小时数，会发生什么情况它们，MySql会自动按照该语句计时

will this above statement include the values with a date of 2013-06-01 as well or only from '2013-06-02', and what happens if the statement stays as is, but then the date values have hours in them, will MySql automatically hours to this statement

推荐答案

Fabio实际上是不正确的，如果要包括小时，分钟和秒的话

Fabio is actually not right, if hours, minutes and seconds will be included this

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

在内部成为

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

因此，您实际上只选择了2013-06-06的1秒，而不是整天选择！

So you actually just select 1 second of 2013-06-06, not the whole day!

当然与BETWEEN相同.要获得2013年6月6日的整天服务，您必须写

Same with BETWEEN of course. To get the whole day of 2013-06-06 you'd have to write

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

或

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

继续，请自行尝试(或将其保存在 sqlfiddle 中):

Go ahead, try it yourself (or see it live in an 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*/

这篇关于在日期之间使用MySql子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！