List:General Discussion« Previous MessageNext Message »
From:BMBasal Date:October 1 2010 8:50pm
Subject:RE: Not to show until a certain date
View as plain text  
Your suggestion seems more elegant. However, you missed the mathematical
meaning of "BETWEEN" in SQL: 
it is inclusive of both lower and upper bounds.

In the case raised by Patrice Olivier-Wilson, when an announcement expires
on announcements_expiredate, it should not show on that date, and
thereafter.

But using BETWEEN, it will show on announcements_expiredate, thus a logical
error.

-----Original Message-----
From: Hank [mailto:heskin@stripped] 
Sent: Friday, October 01, 2010 4:10 PM
To: Joerg Bruehe
Cc: mysql@stripped
Subject: Re: Not to show until a certain date

On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe <joerg.bruehe@stripped>
wrote:
> Hi!
>
>
> Hank wrote:
>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
>> <biz@stripped> wrote:
>>> On 9/28/10 8:33 PM, Chris W wrote:
>>>
>>>> SELECT *
>>>> FROM announcements
>>>> WHERE announcements_expiredate > CURDATE()
>>>> AND announcements_postdate <= CURDATE()
>>>> ORDER BY announcements_expiredate ASC
>>
>> Or how about something like this:
>>
>> SELECT *
>> FROM announcements
>> WHERE  CURDATE() between announcements_postdate and
announcements_expiredate
>> ORDER BY announcements_expiredate ASC
>
> The syntax is correct, but I don't think this statement will be
> optimized as well as the other proposal:
> BETWEEN is intended for "column BETWEEN const1 AND const2",
> whereas your statement is "const BETWEEN column1 AND column2".
>

But that only really matters if there are indexes on the column1 and
column2 fields.

And for the optimizer, wouldn't it make sense to map BETWEEN into two
comparison statements "(column>const1 and column<=const2)"  or
"(const>column1 and const<=column2)" where both scenarios the
optimizer may be able to use indexes on the fields?  It's exactly the
same as the other proposal:

"CURDATE() > announcements_postdate and CURDATE()<=
announcements_expiredate" which still is using two different fields
for the comparisons... so wouldn't both scenarios end up in the exact
same place?

-Hank

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Not to show until a certain datePatrice Olivier-Wilson28 Sep
  • Re: Not to show until a certain datePatrice Olivier-Wilson28 Sep
    • Re: Not to show until a certain dateChris W29 Sep
      • Re: Not to show until a certain datePatrice Olivier-Wilson29 Sep
        • Re: Not to show until a certain dateHank29 Sep
          • Re: Not to show until a certain dateJoerg Bruehe1 Oct
            • Re: Not to show until a certain dateHank1 Oct
              • RE: Not to show until a certain dateBMBasal1 Oct
                • Re: Not to show until a certain dateHank2 Oct
              • Re: Not to show until a certain dateJoerg Bruehe1 Oct