How to subtract 30 days from the current datetime in mysql

Question

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

User · Answer

To anyone who doesn t want to use DATE SUB  use CURRENT DATE   SELECT CURRENT DATE - INTERVAL 30 DAY

User · Answer

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  gt   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

User · Answer

another way  SELECT COUNT    FROM tbl debug WHERE TO DAYS  when    lt  TO DAYS NOW   -30

User · Answer

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

User · Answer

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

User · Answer

SELECT date format current date - INTERVAL 50 DAY   d- b- Y     You can format by using date format in SQL

User · Answer

You can also use   select CURDATE  -INTERVAL 30 DAY

User · Answer

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

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

Examples related to mysql