Select articles from within a month of todays date?

[Perad]

I am having a bit of trouble working out how to do this. I just want to get all articles posted within a month, unfortunately I am not having much luck does anyone know whow to do this?

[Perad]

I forgot to say that times are savedd as datetime in my db

[bubblegum.anarchy]

$month = "2007-06-01"; // SQL date format YYYY-MM-DD

$result = mysql_query($query = "
     SELECT * FROM articles
     WHERE date_created BETWEEN '{$month}' AND last_day('{$month}')") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);

[obsidian]

If you always want only this month's posts, you can just do this:

SELECT * FROM articles WHERE MONTH(date_created) = MONTH();

 

If you want to get all of the articles one month from today, just do this:

SELECT * FROM articles WHERE date_created BETWEEN CURDATE() AND CURDATE() + INTERVAL 1 MONTH;

[bubblegum.anarchy]

This is a little silly and pedantic but anyways:

 

SET @date_created = '2007-06-01';

SELECT benchmark(10000000, month(@date_created) = month(CURRENT_DATE));
# ~ 531ms

SELECT benchmark(10000000, @date_created BETWEEN CURRENT_DATE AND last_day(CURRENT_DATE));
# ~ 219ms

 

The month function appears to take some time.

 

version = 4.1.21-community-nt

[fenway]

To be fair, you're running the MONTH() function twice.

[bubblegum.anarchy]

oh yeah... but take a look at this...

 

SET @date_created = '2007-06-01';

SELECT benchmark(10000000, month(@date_created) = month(CURRENT_DATE));
# ~ 2719ms

SELECT benchmark(10000000, @date_created BETWEEN last_day(CURRENT_DATE - interval 1 month) AND last_day(CURRENT_DATE));
# - 297ms

 

I must have previously not run the SET line... *slaps head*

[fenway]

Well, I'm not surprised, it's a lot of extraction of the month part.