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=gcdmg-mysql-2@m.gmane.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_postdate 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=gcdmg-mysql-2@m.gmane.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_postdate > 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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

Re: Not to show until a certain date

On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
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=gcdmg-mysql-2@m.gmane.org

Re: Not to show until a certain date

Hi!


Hank wrote:
> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
> wrote:
>> On 9/28/10 8:33 PM, Chris W wrote:
>>
>>> SELECT *
>>> FROM announcements
>>> WHERE announcements_expiredate > CURDATE()
>>> AND announcements_postdate <=3D CURDATE()
>>> ORDER BY announcements_expiredate ASC
>=20
> Or how about something like this:
>=20
> SELECT *
> FROM announcements
> WHERE CURDATE() between announcements_postdate and announcements_expir=
edate
> 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

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Not to show until a certain date

On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe wrot=
e:
> Hi!
>
>
> Hank wrote:
>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
>> wrote:
>>> On 9/28/10 8:33 PM, Chris W wrote:
>>>
>>>> SELECT *
>>>> FROM announcements
>>>> WHERE announcements_expiredate > CURDATE()
>>>> AND announcements_postdate <=3D CURDATE()
>>>> ORDER BY announcements_expiredate ASC
>>
>> Or how about something like this:
>>
>> SELECT *
>> FROM announcements
>> WHERE =A0CURDATE() between announcements_postdate and announcements_expi=
redate
>> 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<=3Dconst2)" or
"(const>column1 and const<=3Dcolumn2)" 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()<=3D
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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Not to show until a certain date

Your suggestion seems more elegant. However, you missed the mathematical
meaning of "BETWEEN" in SQL:=20
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@gmail.com]=20
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
wrote:
> Hi!
>
>
> Hank wrote:
>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
>> wrote:
>>> On 9/28/10 8:33 PM, Chris W wrote:
>>>
>>>> SELECT *
>>>> FROM announcements
>>>> WHERE announcements_expiredate > CURDATE()
>>>> AND announcements_postdate <=3D CURDATE()
>>>> ORDER BY announcements_expiredate ASC
>>
>> Or how about something like this:
>>
>> SELECT *
>> FROM announcements
>> WHERE =A0CURDATE() 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<=3Dconst2)" or
"(const>column1 and const<=3Dcolumn2)" 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()<=3D
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

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dlinyu@cs.albany.edu


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Not to show until a certain date

Hi Hank, all!


Hank wrote:
> On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe =
wrote:
>> Hi!
>>
>>
>> Hank wrote:
>>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
>>> wrote:
>>>> On 9/28/10 8:33 PM, Chris W wrote:
>>>>
>>>>> SELECT *
>>>>> FROM announcements
>>>>> WHERE announcements_expiredate > CURDATE()
>>>>> AND announcements_postdate <=3D CURDATE()
>>>>> ORDER BY announcements_expiredate ASC
>>> Or how about something like this:
>>>
>>> SELECT *
>>> FROM announcements
>>> WHERE CURDATE() between announcements_postdate and announcements_exp=
iredate
>>> 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".
>>
>=20
> 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.

>=20
> And for the optimizer, wouldn't it make sense to map BETWEEN into two
> comparison statements "(column>const1 and column<=3Dconst2)" or
> "(const>column1 and const<=3Dcolumn2)" where both scenarios the
> optimizer may be able to use indexes on the fields? It's exactly the
> same as the other proposal:
>=20
> "CURDATE() > announcements_postdate and CURDATE()<=3D
> 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

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@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=3Dgcdmg-mysql-2@m.gmane.o rg

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 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 expire=
s
> on announcements_expiredate, it should not show on that date, and
> thereafter.
>
> But using BETWEEN, it will show on announcements_expiredate, thus a logic=
al
> error.
>
> -----Original Message-----
> From: Hank [mailto:heskin@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
> wrote:
>> Hi!
>>
>>
>> Hank wrote:
>>> On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
>>> wrote:
>>>> On 9/28/10 8:33 PM, Chris W wrote:
>>>>
>>>>> SELECT *
>>>>> FROM announcements
>>>>> WHERE announcements_expiredate > CURDATE()
>>>>> AND announcements_postdate <=3D CURDATE()
>>>>> ORDER BY announcements_expiredate ASC
>>>
>>> Or how about something like this:
>>>
>>> SELECT *
>>> FROM announcements
>>> WHERE =A0CURDATE() 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<=3Dconst2)" =A0or
> "(const>column1 and const<=3Dcolumn2)" where both scenarios the
> optimizer may be able to use indexes on the fields? =A0It's exactly the
> same as the other proposal:
>
> "CURDATE() > announcements_postdate and CURDATE()<=3D
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dlinyu@cs.alba=
ny.edu
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dheskin@gmail.=
com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg