Re: Not to show until a certain date
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. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 (columnconst1 and column=const2) or (constcolumn1 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=arch...@jab.org
RE: Not to show until a certain date
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 (columnconst1 and column=const2) or (constcolumn1 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=arch...@jab.org
Re: Not to show until a certain date
Hi Hank, all! Hank wrote: 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. Correct. But given that the typical question here is how can I make it faster? and the typical answer create proper indexes, we should assume there are (or will be) indexes defined. And for the optimizer, wouldn't it make sense to map BETWEEN into two comparison statements (columnconst1 and column=const2) or (constcolumn1 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? As I wrote: Your syntax is correct. I should have added: And the semantics are correct, too. Your approach is perfectly valid. However, there several discussions about the optimizer at various lists and forums which mostly agree that it might handle some constructs even better. I don't have expert knowledge there, I can't tell what the optimizer will do with the two statements proposed, I just fear it might not handle const BETWEEN column1 AND column2 as clever as you hope. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not to show until a certain date
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 (columnconst1 and column=const2) or (constcolumn1 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
Re: Not to show until a certain date
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 Thank you! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not to show until a certain date
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Not to show until a certain date
Hi... beginner here. Working on a php page and using this $query_announcements = SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() ORDER BY announcements_expiredate ASC ; Client now wants the announcement NOT to show until a specific date. I have an announcements_postdate in the table. Just not sure what the WHERE should be to not show until that date. Thanks much, -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not to show until a certain date
On 9/28/10 10:52 AM, Patrice Olivier-Wilson wrote: Hi... beginner here. Working on a php page and using this $query_announcements = SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() ORDER BY announcements_expiredate ASC ; Client now wants the announcement NOT to show until a specific date. I have an announcements_postdate in the table. Just not sure what the WHERE should be to not show until that date. Thanks much, Figured it out SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() AND announcements.announcements_postdateCURDATE() ORDER BY announcements_expiredate ASC thx -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not to show until a certain date
On 9/28/2010 10:04 AM, Patrice Olivier-Wilson wrote: Figured it out SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() AND announcements.announcements_postdateCURDATE() ORDER BY announcements_expiredate ASC I think you probably should do it like this. SELECT * FROM announcements WHERE announcements_expiredate CURDATE() AND announcements_postdate = CURDATE() ORDER BY announcements_expiredate ASC Otherwise they won't show till after the postdate. I assume you want to display them on the post date and not the next day? This of course assumes your field is of type 'date' and not 'datetime'. Prefixing the field name with the table name is not needed unless you have a join with a table with the same field names. Based on your field naming method it appears as though that won't happen. If it does, it is much less to type and easier to read if you alias the table name. like this.. SELECT * FROM announcements a WHERE a.announcements_expiredate CURDATE() AND a.announcements_postdate=CURDATE() ORDER BY a.announcements_expiredate ASC also it is a good habit to get into to have all filed and table names enclosed in back ticks just in case you have field names that are sql reserved words or otherwise would confuse MySQL. SELECT * FROM `announcements` a WHERE a.`announcements_expiredate` CURDATE() AND a.`announcements_postdate` = CURDATE() ORDER BY a.`announcements_expiredate` ASC Also to me it just makes it easier to read/ understand if you second condition is rewritten like this... AND CURDATE() = announcements_postdate Just my opinion on that. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
