Take the 2-minute tour ×

How do I subtract 30 days from the current datetime in mysql?

WHERE exec_datetime BETWEEN DATEDIFF(NOW() - 30 days) AND NOW();
share|improve this question
What is the point of the AND NOW()? –  minitech May 26 '12 at 1:49
It's the syntax for the BETWEEN value1 AND value2 –  user784637 May 26 '12 at 1:49
@minitech: it's the second operand for BETWEEN –  zerkms May 26 '12 at 1:50
how do I subtract 30 days, the -30 days syntax doesn't work –  user784637 May 26 '12 at 1:51
@zerkms: Ah... (facepalm) –  minitech May 26 '12 at 1:54
add comment

2 Answers

up vote 18 down vote accepted


share|improve this answer
add comment

Let's not use NOW(), you're losing any query caching or optimization because the query is different every time. See the list of functions you should not use here in the docs.

In the below let's assume this table is growing with time. New stuff is getting added and you want to show just the stuff in the last 30 days. This is the most common case.

Note that the date has been added as a string. It is better to add the date in this way from your calling code than to use the NOW() function as it kills your caching.

SELECT * FROM table WHERE exec_datetime >= DATE_SUB('2012-06-12', INTERVAL 30 DAY);

You can use BETWEEN if you really just want stuff from this very second to 30 days before this very second, but that's not a common use case in my experience, so I hope the simplified query can serve you well.

share|improve this answer
So you proposed not no use NOW() and still use it in your answer. o_O PS: your query would return another result, not what OP probably wants –  zerkms May 26 '12 at 1:56
@zerkms How would this query produce a different result set than your query? –  user784637 May 26 '12 at 2:05
@user784637: uhm, BETWEEN and >= are different operators, you know? My query limits the result set for 30 days ago AND today, this everything after 30 days ago. So tomorrow records won't match for mine and your original query, but will match by this answer –  zerkms May 26 '12 at 3:07
Ok, my bad. Typically such queries are in live tables that are growing with time. That is why I dropped the between. I've clarified my answer above. –  Joseph Lust May 26 '12 at 10:33
add comment

Your Answer


