Help writing a WHERE clause 1

[eaglesphan]

I would like to write a SELECT statement where I can get the last 30 days worth of press releases. Reading the manual I found this code example:

SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

This is close to what I'm looking for but not quite. I have solved this problem in PHP using two variables for the current date and current date minus a month and then using WHERE date BETWEEN $var1 AND $var2. But I'd like to be able to get MySQL to do all the work and not PHP. My problem is I get messed up if the month is January and subtracting 1 gives me zero, not the 12 I need. Is it possible to do this using functions rather than variables? Thanks.

 

[TonyGroves]

How about:
[tt]
WHERE date >= CURDATE() - INTERVAL 30 DAY
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[eaglesphan]

OK that works. One more question. Is there a way to make it so that when you insert a record, the date column (which is of type date) automatically updates itself with the current date kind of like auto-increment for the id column?

 

[TonyGroves]

You can define a TIMESTAMP field, which, when you insert or update the record, will be automatically set to the current date and time unless you specify a value.

If you want to stick with a DATE field, you will have to specify a value each time:[tt]
INSERT tbl (id,datefld) VALUES (823743,CURDATE())[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[eaglesphan]

OK that helps. Thanks.

 

[BB101]

I never use mysql's date fields, they are too slow and too awkward to work with. Instead, use the trusty unix epoch (thats the number of seconds since Jan 1 1970), its very easy to use with most languages as they come with things like PHP's date function. Feed it a format for the date, and the date as a unix timestamp and you get the correct date formatted in a desirable way.

As for comparing the dates, well, 60 seconds multiplied by 60 minutes multiplied by 24 hours is 86400, I'll leave the rest to you.

--BB