MySQL previous month query

return rows which are from last month

[resistanceM]

Hi all

im trying to retrieve rows from my table where the month is last month, but it returns zero results.

i currently have:

$result = mysql_query('SELECT * FROM tickets WHERE (month(created) = (month(now())-1))');

anyone know why it might not be working? any help greatly appreciated thanks again.

"created" is the name of the column in the table which holds the date....just to make things clearer

[edited by: resistanceM at 11:59 pm (utc) on Jan. 5, 2008]

[phranque]

since this doesn't wrap itself i think you have to make allowances for january queries, where:
(month(now())-1) = 0
and you should therefore not expect any rows returned.

maybe you can make it work with date_format, period_add and period_diff...

[ZydoSEO]

Try something like:

SELECT * FROM tickets
WHERE MONTH(created) = MONTH(DATE_ADD(now(), INTERVAL -1 MONTH))

I believe you want to be using DATE_ADD to accomplish this.

[edited by: ZydoSEO at 4:44 pm (utc) on Jan. 6, 2008]

[rocknbil]

Two things.

now() includes the full date and time stamp. I'd only use it if that's the format of your fields. I'd use curdate() instead.

I would use date_sub.

select * from tickets where created = date_sub(curdate(), interval 1 month);

However, this will only return fields for tickets sold exactly a month ago from today. If you want last month's tickets up to today,

select * from tickets where created >= date_sub(curdate(), interval 1 month) and created <= date_sub(curdate(), interval 1 day);

[jtara]

select * from tickets where created >= date_sub(curdate(), interval 1 month) and created <= date_sub(curdate(), interval 1 day);

Note that this is the only solution given that can make use of an index on "created".

Any solution that only examines a date part (rather than comparing date values) is going to require an exhaustive read of the entire table.

[resistanceM]

thanks alot for the advice everyone.