MySQL-BETWEEN将不会选择正确的结果 [英] MySQL - BETWEEN will not select correct results

问题描述

我正在尝试选择两个日期之间的行.首先，这是我的数据:

I am trying to select rows that are in between two dates. First, here is my data:

punch_id    eid     time    unixtime    punch_type  day     date        doy
135          2      12:53   1314723193  0            4   08/28/2011     241  
134          2      12:53   1314723190  3            4   08/31/2011     241
133          2      12:53   1314723187  2            4   08/20/2011     241
132          2      12:52   1314723125  1            4   08/30/2011     241

我已经尝试了这两个查询.

I have tried these two queries.

SELECT * FROM `punches` WHERE `date` >= '08/20/11' AND `date` <= '08/31/11'

SELECT * FROM `punches` WHERE `date` BETWEEN '08/20/11' AND '08/31/11'

这些都不选择包含日期08/31/11的行.虽然它选择08/20/11.我尝试以另一种方式以及在运行查询时使用它:

Neither of these select the rows containing the date 08/31/11. It selects the 08/20/11 ones though. I tried to use it another way and when I run the query:

SELECT * FROM `punches` WHERE `date` >= '08/10/11' AND `date` <= '08/20/11'

我再次没有得到正确的结果:20号再次被遗漏了.我执行此操作的方式有什么问题?

I again do not get the correct result: the 20th is left out once again. What is the problem with the way I am executing this?

推荐答案

请参见正如其他人提到的那样，您的主要问题不是时间问题.一些解决方案:

As others have mentioned, your primary problem is not accounting for the time. A few options to handle that:

1. 使用函数将DateTime转换为Date.我不建议使用此选项，因为它可能会使功能变为非-sargeable .

扩展您的BETWEEN以明确包括一天中的最后时刻:(注意:这是MS SQL可以存储的最新值，不知道MySQL是否具有相同的值)

Expand your BETWEEN to explicitly include the last moment of the day: (note: this is the latest possible value that MS SQL can store, don't know if MySQL has the same value)

SELECT * FROM `punches` WHERE `date` 
BETWEEN '08/20/11 00:00:00.000' AND '08/31/11 23:59:59.997'

使用<作为上限值

Use a < for the upper value

SELECT * FROM `punches` WHERE `date` >= '08/20/11' AND `date` < '09/01/11'

我实际上认为，在大多数情况下，最后一个更容易.

I actually think that last one is easier, in most situations.

我想您还可以做其他事情，例如更改列的数据类型，但是我在这里假设您只是对更改查询感兴趣.

I suppose you could do other things, like change the datatype of the column, but I've assumed here that you're just interested in changing the query.

**免责声明:我是MS SQL专家，不是MySQL

** Disclaimer: I'm a MS SQL guy, not MySQL

这篇关于MySQL-BETWEEN将不会选择正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！