How to subtract 30 days from the current datetime in mysql?
MysqlMysql Problem Overview
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();
Mysql Solutions
Solution 1 - Mysql
SELECT * FROM table
WHERE exec_datetime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
Solution 2 - Mysql
To anyone who doesn't want to use DATE_SUB, use CURRENT_DATE:
SELECT CURRENT_DATE - INTERVAL 30 DAY
Solution 3 - Mysql
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.
Solution 4 - Mysql
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
Solution 5 - Mysql
You can also use
select CURDATE()-INTERVAL 30 DAY
Solution 6 - Mysql
SELECT date_format(current_date - INTERVAL 50 DAY,'%d-%b-%Y')
You can format by using date format in SQL.
Solution 7 - Mysql
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.
Solution 8 - Mysql
another way
SELECT COUNT(*) FROM tbl_debug WHERE TO_DAYS(`when`) < TO_DAYS(NOW())-30 ;