I would argue that it's not a logical error. Typically, in my experience when something expires, the date of expiry is inclusive, and any date after expiry is not. Take any discount, coupon, or promotion -- if it expires on December 31, it's still available on that day. If an article expires on December 31, I would interpret that as still being valid on December 31, and not valid on January 1. Another example is Credit Cards expire on the last day of the month of expiry, but they are still valid on that date.
But assuming for a moment that for this application, the date of expiry is not inclusive (i.e. that expirydate actually means "date of deletion"), one could still do: curdate() between postdate and date_sub(expiredate, INTERVAL 1 day) And to reply to Joerg Bruehe, I have used this method of BETWEEN many times over the years without any performance or optimization issues... but sure, that doesn't mean they will never exist for other applications, but it has worked well for me. Best, -Hank On Fri, Oct 1, 2010 at 4:50 PM, BMBasal <bmb37...@gmail.com> wrote: > 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:hes...@gmail.com] > Sent: Friday, October 01, 2010 4:10 PM > To: Joerg Bruehe > Cc: mysql@lists.mysql.com > Subject: Re: Not to show until a certain date > > On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe <joerg.bru...@oracle.com> > wrote: >> Hi! >> >> >> Hank wrote: >>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson >>> <b...@biz-comm.com> 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=li...@cs.albany.edu > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=hes...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
