CODEWITHSUNDEEP
mysql
user784637
user784637

Reputation: 16092

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

Upvotes: 303

Views: 419380

Answers (8)

Eric Leschinski
Eric Leschinski

Reputation: 153822

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

Upvotes: 39

Noby Nirmal
Noby Nirmal

Reputation: 349

You can also use 

select CURDATE()-INTERVAL 30 DAY

Upvotes: 21

Joseph Lust
Joseph Lust

Reputation: 19975

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.

Upvotes: 28

Doug Fir
Doug Fir

Reputation: 21204

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

SELECT CURRENT_DATE - INTERVAL 30 DAY

Upvotes: 187

Varaj Vignesh
Varaj Vignesh

Reputation: 341

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

You can format by using date format in SQL.

Upvotes: 3

Pacerier
Pacerier

Reputation: 89593

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.

Upvotes: 2

zzapper
zzapper

Reputation: 5043

another way

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

Upvotes: 0

zerkms
zerkms

Reputation: 254886

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

Upvotes: 415

Related Questions