[mysql] How to subtract 30 days from the current datetime in mysql?

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

SELECT * FROM table
WHERE exec_datetime BETWEEN DATEDIFF(NOW() - 30 days) AND NOW();

SELECT date_format(current_date - INTERVAL 50 DAY,'%d-%b-%Y')

You can format by using date format in SQL.

If you only need the date and not the time use:

select*from table where exec_datetime
between subdate(curdate(), 30)and curdate();

Since curdate() omits the time component, it's potentially faster than now() and more "semantically correct" in cases where you're only interested in the date.

Also, subdate()'s 2-arity overload is potentially faster than using interval. interval is meant to be for cases when you need a non-day component.

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

In the code below, let's assume this table is growing with time. New stuff is 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.

MySQL subtract days from now:

select now(), now() - interval 1 day

Prints:

2014-10-08 09:00:56     2014-10-07 09:00:56

Other Interval Temporal Expression Unit arguments:

https://dev.mysql.com/doc/refman/5.5/en/expressions.html#temporal-intervals

select now() - interval 1 microsecond 
select now() - interval 1 second 
select now() - interval 1 minute 
select now() - interval 1 hour 
select now() - interval 1 day 
select now() - interval 1 week 
select now() - interval 1 month 
select now() - interval 1 year 

To anyone who doesn't want to use DATE_SUB, use CURRENT_DATE:

SELECT CURRENT_DATE - INTERVAL 30 DAY

You can also use

select CURDATE()-INTERVAL 30 DAY

another way

SELECT COUNT(*) FROM tbl_debug WHERE TO_DAYS(`when`) < TO_DAYS(NOW())-30 ;