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

The Question :

209 people think this question is useful

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();


The Question Comments :

The Answer 1

320 people think this answer is useful
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

The Answer 2

137 people think this answer is useful

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

SELECT CURRENT_DATE - INTERVAL 30 DAY



The Answer 3

21 people think this answer is useful

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.

The Answer 4

19 people think this answer is useful

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



The Answer 5

18 people think this answer is useful

You can also use

select CURDATE()-INTERVAL 30 DAY



The Answer 6

1 people think this answer is useful
SELECT date_format(current_date - INTERVAL 50 DAY,'%d-%b-%Y')



You can format by using date format in SQL.

The Answer 7

0 people think this answer is useful

another way

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



The Answer 8

0 people think this answer is useful

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.

Tags: