List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 23 2013 10:41pm
Subject:RE: Bug in BETWEEN same DATETIME
View as plain text  
I use this; it keeps me out of trouble whether I am using
* MySQL's DATE vs DATETIME vs TIMESTAMP
* Sybase dates (to minute or to millisecond, hence :59:59 does not work)
* leap year

    WHERE dt >= ?
      AND dt <  ? + INTERVAL ? DAY

I fill in the first two "?" with the same starting date.

> -----Original Message-----
> From: Michael Dykman [mailto:mdykman@stripped]
> Sent: Thursday, May 23, 2013 2:56 PM
> To: MySql
> Subject: Re: Bug in BETWEEN same DATETIME
> 
>   >>      where cast(transaction_date as date) BETWEEN '2013-04-16' AND
> 
> This approach might be problematic in that it requires that every row
> in the source table be examined so that it's transaction_date can be
> casted.
>  The original formulation is more efficient as it allows an index on
> transaction_date to be used, if one exists.
>    >> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND
> '2013-04-16 23:59:59'
> 
> Although you probably get the result you want by just incrementing the
> day on the upper-limit.
>     WHERE `transaction_date` BETWEEN '2013-04-16 AND '2013-04-17'
> 
>  - michael dykman
> 
> 
> On Thu, May 23, 2013 at 5:07 PM, Peterson, Timothy R <
> timothy_r_peterson@stripped> wrote:
> 
> > You probably want
> >         where cast(transaction_date as date) BETWEEN '2013-04-16' AND
> > '2013-04-16'
> > That works on my test case
> >
> > You could also change the where clause to be >= date and < date+1
> >
> >
> >
> > -----Original Message-----
> > From: Daevid Vincent [mailto:daevid@stripped]
> > Sent: Thursday, May 23, 2013 3:56 PM
> > To: mysql@stripped
> > Subject: Bug in BETWEEN same DATETIME
> >
> > 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#opera
> > to
> > r_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)?
> >
> > This e-mail, including attachments, may include confidential and/or
> > proprietary information, and may be used only by the person or entity
> > to which it is addressed. If the reader of this e-mail is not the
> > intended recipient or his or her authorized agent, the reader is
> > hereby notified that any dissemination, distribution or copying of
> > this e-mail is prohibited. If you have received this e-mail in error,
> > please notify the sender by replying to this message and delete this
> e-mail immediately.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql
> >
> >
> 
> 
> --
>  - michael dykman
>  - mdykman@stripped
> 
>  May the Source be with you.
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