t; Sent: Friday, May 24, 2013 11:08 AM
> To: mysql@lists.mysql.com
> Subject: Re: Bug in BETWEEN same DATETIME
>
> >>>> 2013/05/24 09:49 -0400, shawn green >>>>
> Or we could coerce datetime values back to their date values when both
> are being used. The
2013/05/24 09:49 -0400, shawn green
Or we could coerce datetime values back to their date values when both are
being used. The trick now becomes choosing between rounding the datetime value
(times past noon round to the next date) or do we use the floor() function all
the time.
<<<
Hi Rick,
Thank you for continuing this. It brings up a few good points.
On 5/24/2013 12:17 PM, Rick James wrote:
For years (even decades), I have stayed out of trouble by assuming a 'date'
represents the instant in time corresponding to midnight at the start of that
day. In MySQL (until 5.6)
g
event, an appointment (potentially in a diff timezone), train schedule, etc.
> -Original Message-
> From: shawn green [mailto:shawn.l.gr...@oracle.com]
> Sent: Friday, May 24, 2013 6:50 AM
> To: mysql@lists.mysql.com
> Subject: Re: Bug in BETWEEN same DATETIME
>
>
Hello Rick,
On 5/23/2013 7:08 PM, Rick James wrote:
Watch out for CAST(), DATE(), and any other function. In a WHERE clause, if
you hide an indexed column inside a function, the index cannot be used for
optimization.
INDEX(datetime_col)
...
WHERE DATE(datetime_col) = '2013-01-01'
wi
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)
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:dae...@daevid.com]
Sent: Thursday, May 23, 2
>> 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
transactio
On 5/23/2013 4:55 PM, Daevid Vincent 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.
iginal Message-
> From: shawn green [mailto:shawn.l.gr...@oracle.com]
> Sent: Thursday, May 23, 2013 3:50 PM
> To: mysql@lists.mysql.com
> Subject: Re: Bug in BETWEEN same DATETIME
>
>
>
> On 5/23/2013 4:55 PM, Daevid Vincent wrote:
> > I just noticed what I
he same starting date.
> -Original Message-
> From: Michael Dykman [mailto:mdyk...@gmail.com]
> 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&#x
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 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
12 matches
Mail list logo
