470,591 Members | 1,446 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

selecting date records

What is the most efficient way to select records based on a field called NOW
which contains a date or a datetimestamp

I use:
SELECT * FROM `TBL` WHERE
DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( );

Is this the best way?

TIA
Nicolaas

Mar 13 '06 #1
2 2129
>What is the most efficient way to select records based on a field called NOW
which contains a date or a datetimestamp

I use:
SELECT * FROM `TBL` WHERE
DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( );

Is this the best way?


I think something like:

SELECT * FROM `TBL` WHERE
`TBL`.`NOW` > DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

might be faster if the query optimizer doesn't deal with it. With
the first query, you evaluate DATE_ADD() once for each row in the
table. With the second, you evaluate DATE_SUB() once. In general,
comparing a table field against a computable run-time constant is
more likely to use an index than comparing some function of a table
field against something. Or, they might be equivalent in speed.

Gordon L. Burditt
Mar 13 '06 #2
Gordon Burditt wrote:
What is the most efficient way to select records based on a field
called NOW which contains a date or a datetimestamp

I use:
SELECT * FROM `TBL` WHERE
DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( );

Is this the best way?
I think something like:

SELECT * FROM `TBL` WHERE
`TBL`.`NOW` > DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

Thank you. That makes a lot of sense.
Nicolaas

Mar 13 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by C.F. Scheidecker Antunes | last post: by
reply views Thread by allyn44 | last post: by
4 posts views Thread by Eugene Anthony | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.