List:General Discussion« Previous MessageNext Message »
From:Andrew Moore Date:May 23 2013 9:25pm
Subject:Re: Bug in BETWEEN same DATETIME
View as plain text  
Sorry, that was meant to be;

WHERE (new column stored as date) = '2013-04-16'


On Thu, May 23, 2013 at 10:16 PM, Andrew Moore <eroomydna@stripped> wrote:

> Personally I don't share your view that it's a bug. Omitting the time
> results in midnight by default so this screws between because there's no
> time between 00:00:00 and 00:00:00.
>
> Are you having operational issues here or are you simply fishing for bugs?
>
> WHERE  `transaction_date` = DATE(datetime)
> or
> WHERE  `transaction_date` = (new column stored as date)
>
>
>
>
>
> On Thu, May 23, 2013 at 9:55 PM, Daevid Vincent <daevid@stripped> wrote:
>
>> I just noticed what I consider to be a bug; and related, has this been
>> fixed
>> in later versions of MySQL?
>>
>> We are using:
>> mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2
>>
>> If you use BETWEEN and the same date for both parts (i.e. you want a
>> single
>> day) it appears that the operator isn't smart enough to consider the full
>> day in the cases where the column is a DATETIME
>>
>>
>> http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_be
>> tween
>>
>> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
>>
>> I actually have to format it like this to get results
>>
>> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
>> 11:59:59'
>>
>> As it appears that in the first instance it defaults the time to 00:00:00
>> always, as verified by this:
>>
>> WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
>>
>> So,  I think it's probably safe to assume that if someone is using the
>> BETWEEN on datetime columns, their intent more often than not is to get
>> the
>> full 24 hour period, not the 0 seconds it currently pulls by default.
>>
>> I also tried these hacks as per the web page above, but this doesn't yield
>> results either
>>
>> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
>> CAST('2013-04-16' AS DATE)
>> WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
>> CAST('2013-04-16' AS DATETIME)
>>
>> This one works, but I fail to see how it's any more beneficial than using
>> a
>> string without the CAST() overhead?
>>
>> WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME)
>> AND
>> CAST('2013-04-16 11:59:59' AS DATETIME)
>>
>> Or is there some other magical incantation that is supposed to be used
>> (without me manually appending the time portion)?
>>
>
>

Thread
Bug in BETWEEN same DATETIMEDaevid Vincent23 May
  • RE: Bug in BETWEEN same DATETIMETimothy R Peterson23 May
    • Re: Bug in BETWEEN same DATETIMEMichael Dykman23 May
      • RE: Bug in BETWEEN same DATETIMERick James23 May
  • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
    • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
  • Re: Bug in BETWEEN same DATETIMEshawn green23 May
    • RE: Bug in BETWEEN same DATETIMERick James23 May
      • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • RE: Bug in BETWEEN same DATETIMERick James24 May
          • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • Re: Bug in BETWEEN same DATETIMEhsv24 May
          • RE: Bug in BETWEEN same DATETIMERick James29 May