MySQL Date Field Help Needed

[Ralph Freshour]

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

....doesn't do anything...

Thanks for any help.

[Benjamin Esham]

* Ralph Freshour <ra***@primemail.com>:

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

...doesn't do anything...

I'm not too familiar with date calculations in MySQL, but perhaps changing
"creation_date =" to "creation_date >=" will solve your problem.

--
Benjamin D. Esham { http://bdesham.net
bd*****@iname.com } AIM: bdesham 1 2 8
"Hmm... Earl Grey tea. Maybe that's how an old
bald guy can kick the Borg's collective ass."
-- bsharitt on Slashdot

[Ralph Freshour]

No, that didn't work...any other thoughts???
On Sun, 31 Aug 2003 22:35:55 -0400, Benjamin Esham <bd*****@iname.com>
wrote:

* Ralph Freshour <ra***@primemail.com>:

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

...doesn't do anything...

I'm not too familiar with date calculations in MySQL, but perhaps changing
"creation_date =" to "creation_date >=" will solve your problem.

[Jon Kraft]

Ralph Freshour <ra***@primemail.com> wrote:

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

You're searching rows which are exactly 30 days old - if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date, INTERVAL
-30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date, INTERVAL
30 DAY);

HTH;
JOn

[Ralph Freshour]

Hi Jon -

I tried both of your suggestions but neither worked - I doubled
checked my MySQL table col (field) data and all the dates are of Date
type and they are all in the month of August 2003 (varying days; 5th,
10th, etc.)

I don't have to tell the query to compare the last 30 days from today?
That's assumed in the query right?
On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jo*@jonux.co.uk> wrote:

Ralph Freshour <ra***@primemail.com> wrote:

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

You're searching rows which are exactly 30 days old - if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date, INTERVAL
-30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date, INTERVAL
30 DAY);

HTH;
JOn

[Jon Kraft]

Ralph Freshour <ra***@primemail.com> wrote:

On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jo*@jonux.co.uk> wrote:

Ralph Freshour <ra***@primemail.com> wrote:

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

You're searching rows which are exactly 30 days old - if you want all rows
within the last 30 days use:

SELECT * FROM basics WHERE creation_date > DATE_ADD(creation_date,
INTERVAL -30 DAY);

or:

SELECT * FROM basics WHERE creation_date > DATE_SUB(creation_date,
INTERVAL 30 DAY);

I tried both of your suggestions but neither worked - I doubled
checked my MySQL table col (field) data and all the dates are of Date
type and they are all in the month of August 2003 (varying days; 5th,
10th, etc.)

I don't have to tell the query to compare the last 30 days from today?
That's assumed in the query right?

Hi Ralph,

Sorry about that, you're absolutely right, you have to tell the period of
days from now, not from the datefield!

SELECT * FROM basics WHERE creation_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

HTH;
JOn

[Gary Petersen]

On Sun, 31 Aug 2003 20:05:30 -0500, in message
<qk********************************@4ax.com>, the AI program named "Ralph
Freshour" <ra***@primemail.com> randomly printed:

I have a Date type in my MySQL table - I'm trying to do a query on all
rows within the last 30 days based on that Date field - I'm having
trouble figuring out how to form the query???

$php_SQL = "SELECT * FROM basics WHERE creation_date =
DATE_ADD(creation_date, INTERVAL -30 DAY)";

...doesn't do anything...

Thanks for any help.

Untested:

select * from basics where creation_date >=
date_add(now(), interval -30 day);