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(creati on_date, INTERVAL -30 DAY)";

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

Thanks for any help.

[Benjamin Esham]

Re: MySQL Date Field Help Needed

* Ralph Freshour <ralph@primemai l.com>:
[color=blue]
> 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(creati on_date, INTERVAL -30 DAY)";
>
> ...doesn't do anything...[/color]

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

--
Benjamin D. Esham { http://bdesham.net
bdesham@iname.c om } 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]

Re: MySQL Date Field Help Needed

No, that didn't work...any other thoughts???


On Sun, 31 Aug 2003 22:35:55 -0400, Benjamin Esham <bdesham@iname. com>
wrote:
[color=blue]
>* Ralph Freshour <ralph@primemai l.com>:
>[color=green]
>> 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(creati on_date, INTERVAL -30 DAY)";
>>
>> ...doesn't do anything...[/color]
>
>I'm not too familiar with date calculations in MySQL, but perhaps changing
>"creation_da te =" to "creation_d ate >=" will solve your problem.[/color]

[Jon Kraft]

Re: MySQL Date Field Help Needed

Ralph Freshour <ralph@primemai l.com> wrote:
[color=blue]
> 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(creati on_date, INTERVAL -30 DAY)";[/color]

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(creati on_date, INTERVAL
-30 DAY);

or:

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

HTH;
JOn

[Ralph Freshour]

Re: MySQL Date Field Help Needed

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 <jon@jonux.co.u k> wrote:
[color=blue]
>Ralph Freshour <ralph@primemai l.com> wrote:
>[color=green]
>> 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(creati on_date, INTERVAL -30 DAY)";[/color]
>
>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(creati on_date, INTERVAL
>-30 DAY);
>
>or:
>
>SELECT * FROM basics WHERE creation_date > DATE_SUB(creati on_date, INTERVAL
>30 DAY);
>
>HTH;
>JOn[/color]

[Jon Kraft]

Re: MySQL Date Field Help Needed

Ralph Freshour <ralph@primemai l.com> wrote:
[color=blue]
> On Mon, 01 Sep 2003 10:24:33 +0100, Jon Kraft <jon@jonux.co.u k> wrote:
>[color=green]
>>Ralph Freshour <ralph@primemai l.com> wrote:
>>[color=darkred]
>>> 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(creati on_date, INTERVAL -30 DAY)";[/color]
>>
>>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(creati on_date,
>>INTERVAL -30 DAY);
>>
>>or:
>>
>>SELECT * FROM basics WHERE creation_date > DATE_SUB(creati on_date,
>>INTERVAL 30 DAY);[/color]
>
> 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?[/color]

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]

Re: MySQL Date Field Help Needed

On Sun, 31 Aug 2003 20:05:30 -0500, in message
<qk65lvccqlklds rpbtuf4354lafi0 mc70e@4ax.com>, the AI program named "Ralph
Freshour" <ralph@primemai l.com> randomly printed:
[color=blue]
> 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(creati on_date, INTERVAL -30 DAY)";
>
> ...doesn't do anything...
>
> Thanks for any help.[/color]

Untested:

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