blocks|key|4374944|text|作为日期时间处理的日期假定有00:00:00+:00小时:分钟:秒部分。因此，2011-09-30+14:04:00实际上是>=+2011-09-30。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4374945|要执行您想要的操作，请尝试：|4374946|SELECT+date_start
FROM+`mytable`
WHERE+date(date_start)+>=+"2011-09-19"
AND+date(date_start)+<=+"2011-09-30"
LIMIT+0+,+30|code-block|syntax|javascript|4374947|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

A date treated as a datetime is assumed to have a 00:00:00 hours:minutes:seconds part. Thus, 2011-09-30 14:04:00 is in fact >= 2011-09-30.

To do what you want, try:

<pre><code>SELECT date_start
FROM `mytable`
WHERE date(date_start) &gt;= "2011-09-19"
AND date(date_start) &lt;= "2011-09-30"
LIMIT 0 , 30
</code></pre>

blocks|key|4657428|text|时间也被考虑在内。你应该像这样提到这一点|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4657429|SELECT+date_start
FROM+`mytable`
WHERE+date_start+>=+"2011-09-19"
AND+date_start+<=+"2011-09-30+23:59:59"
LIMIT+0+,+30|code-block|syntax|javascript|4657430|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Time is also considered. you should mention this like 

<pre><code>SELECT date_start
FROM `mytable`
WHERE date_start &gt;= "2011-09-19"
AND date_start &lt;= "2011-09-30 23:59:59"
LIMIT 0 , 30
</code></pre>

blocks|key|612237|text|2011-09-30将被解释为2011-09-30+00:00:00。尝试将范围的末尾设置为2011-09-30+23:59:59，这样就可以了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|612238|entityMap^0|0|A|F|J|1A|J|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

<code>2011-09-30</code> will be interpreted as <code>2011-09-30 00:00:00</code>. Try setting the end of the range as <code>2011-09-30 23:59:59</code> and you should be good.

blocks|key|2759673|text|就我个人而言，我将date和time元素拆分为两个单独的字段date_start和time_start，这将使您的查询能够按照编码正确工作，还允许您执行更复杂和有趣的与日期相关的活动，如我对以下问题的回答中所解释的那样。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2759674|Select+all+months+within+given+date+span,+including+the+ones+with+0+values|2759675|但是，如果您不能对系统的工作方式进行太多更改，那么在您希望过滤的日期上添加一天，然后进行<而不是<=比较也是可行的。|2759676|WHERE+date_start+>=+"2011-09-19"
++++AND+date_start+<+"2011-10-01"|code-block|syntax|javascript|2759677|这将比在过滤器中执行日期(Date_start)更快，因为MySQL将无法使用任何索引，因为您正在对可以索引的字段执行函数。|2759678|希望这能有所帮助。|2759679|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3093924/select-all-months-within-given-date-span-including-the-ones-with-0-values^0|U|A|15|A|0|0|22|0|0|18|1|1C|2|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@$9|16|A|17|1|18]]|E|$]]|$1|H|3|I|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|1E|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1F|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1G|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

Personally, I would split the date and time elements into two separate fields, <code>date_start</code> and <code>time_start</code>, which would then enable your query to work correctly as coded, and also allow you to do more complicated and interesting date related activities such as are explained on my answer to the following question.

<a href="https://stackoverflow.com/questions/3093924/select-all-months-within-given-date-span-including-the-ones-with-0-values">Select all months within given date span, including the ones with 0 values</a>

If however you can't change much about the way the system works, adding a single day to the date you wish to filter by, and then doing a <code>&lt;</code> instead of an <code>&lt;=</code> comparison would also work.

<pre><code>WHERE date_start &gt;= "2011-09-19"
 AND date_start &lt; "2011-10-01"
</code></pre>

This will work faster than performing a DATE(date_start) within your filter, as MySQL will be unable to use any indexes as you are performing a function on the fields that could be indexed.

Hope that helps.

blocks|key|609897|text|date_start+<=+'2011-09-30'表示包含日期2011-09-30+00:00:00，但不包括2011-09-30+00:00:01和更高版本。您可以使用<运算符加上常用的日期算法来解决问题：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|609898|SELECT+date_start
FROM+++mytable
WHERE++date_start+>=+'2011-09-19'
AND++++date_start+<++'2011-09-30'+%2B+INTERVAL+1+DAY
LIMIT++0,+30|code-block|syntax|javascript|609899|entityMap^0|0|Q|W|J|1K|J|2E|1|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

<code>date_start &lt;= '2011-09-30'</code> implies that the date <code>2011-09-30 00:00:00</code> is included but <code>2011-09-30 00:00:01</code> and later are not. You can use the <code>&lt;</code> operator plus come commonsense date arithmetic for a workaround:

<pre><code>SELECT date_start
FROM mytable
WHERE date_start &gt;= '2011-09-19'
AND date_start &lt; '2011-09-30' + INTERVAL 1 DAY
LIMIT 0, 30
</code></pre>

blocks|key|612269|text|如果您在查询中使用+between+语句，那么它将检索位于起点和终点之间的所有记录。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|612270|例如：|612271|SELECT+date_start
FROM+`mytable`
WHERE+date_start+between+'2011-09-19'and+'2011-09-30:23:59:59'
LIMIT+0+,+30|code-block|syntax|javascript|612272|只要根据你的要求试试就行了。|612273|entityMap^0|9|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

if you are using between statement in query then it will retrieve all the records which are in between starting and endpoints.

For example: 

<pre><code>SELECT date_start
FROM `mytable`
WHERE date_start between '2011-09-19'and '2011-09-30:23:59:59'
LIMIT 0 , 30
</code></pre>

Just try this as per your requirement.

I have this following rows in mytable

<pre><code>date_start
-----------
2011-09-20 13:00:00
2011-10-01 13:14:00
2011-10-09 13:16:00
2011-09-27 15:00:00
2011-09-30 14:04:00
2011-10-03 14:00:00
</code></pre>

 
I have this query:

<pre><code>SELECT date_start
FROM `mytable`
WHERE date_start &gt;= "2011-09-19"
AND date_start &lt;= "2011-09-30"
LIMIT 0 , 30
</code></pre>

The problem here is it only outputs:

<pre><code>2011-09-20 13:00:00
2011-09-27 15:00:00
</code></pre>

I expect this result:

<pre><code> 2011-09-20 13:00:00
 2011-09-27 15:00:00
 2011-09-30 14:04:00
</code></pre>

Because if we look at the query it should include this as the result: <code>2011-09-30 14:04:00</code>
 What am I doing wrong in here?

I also used the <code>BETWEEN</code> in mysql but the output is still the same.

Any help would be greatly appreciated and rewarded.

Thanks!

mysql between dates getting the result

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在mytable中有以下行date_start-----------2011-09-20 13:00:002011-10-01 13:14:002011-10-09 13:16:002011-09-27 15:00:002011-09-30 14:04:002011-10-03 14:00:00我有这样的疑问：SELECT date_startFROM `mytable`WHERE date_s

问mysql在日期之间获取结果
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql在日期之间获取结果EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql在日期之间获取结果
EN