MySQL select all rows from last month until now - 1 month for comparative purposes

Question

I need some help writing a MySQL query to show me rows from last month  but not the whole month  only up and until the same day  hour and minute as it is now    but 1 month before   So for example  if today is 5 19 at 5 25pm I need to select rows from midnight 12 00am 4 1 to 5 25pm of 4 19  from the same year too of course     Thanks

User · Accepted Answer

You can get the first of the month  by calculating the last day of the month before and add one day  It is awkward  but I think it is better than formatting a date as string and use that for calculation   select      from   yourtable t where      Greater or equal to the start of last month      t date  gt   DATE ADD LAST DAY DATE SUB NOW    INTERVAL 2 MONTH    INTERVAL 1 DAY  and      Smaller or equal than one month ago      t date  lt   DATE SUB NOW    INTERVAL 1 MONTH

User · Answer

SELECT    FROM table  WHERE date BETWEEN      ADDDATE LAST DAY DATE SUB NOW   INTERVAL 2 MONTH    INTERVAL 1 DAY       AND DATE SUB NOW   INTERVAL 1 MONTH     See the docs for info on DATE SUB  ADDDATE  LAST DAY and other useful datetime functions

User · Answer

Simple code please check  SELECT   FROM table name WHERE created  lt    NOW   - INTERVAL 1 MONTH

User · Answer

maybe like this - all last month DATE recDate  BETWEEN DATE ADD LAST DAY DATE SUB CURDATE    INTERVAL 2 MONTH    INTERVAL 1 DAY  AND LAST DAY DATE SUB CURDATE    INTERVAL 1 MONTH

User · Answer

This is an example of a MySQL date operation relevant to your question   SELECT DATE ADD  now      INTERVAL -1 MONTH      The above will return date time one month ago  So  you can use it  as follows   SELECT    FROM your table  WHERE Your Date Column BETWEEN  2011-01-04       AND DATE ADD NOW     INTERVAL -1 MONTH

User · Answer

SELECT    FROM        lt table name gt   WHERE        lt date field gt   BETWEEN       DATE SUB NOW    INTERVAL 1 MONTH  AND NOW      Similarly  You can select records for 1 month  2 months etc

User · Answer

My solution was to avoid using NOW   when writing sql with your programming language  and substitute with a string   The problem with NOW   as you indicate is it includes current time   So to capture from the beginning of the query day  0 hour and minute  instead of   r date  lt   DATE SUB NOW    INTERVAL 99 DAY    I did  php     current sql date   date  Y-m-d 00 00 00      in the sql    sql x    r date  lt   DATE SUB   current sql date   INTERVAL 99 DAY     With that  you will be retrieving data from midnight of the given day

User · Answer

You could use a WHERE clause like   WHERE DateColumn BETWEEN     CAST date format date sub NOW    INTERVAL 1 MONTH    Y- m-01   AS date      AND     date sub now    INTERVAL 1 MONTH

User · Answer

Getting one month ago is easy with a single MySQL function   SELECT DATE SUB NOW    INTERVAL 1 MONTH     or  SELECT NOW   - INTERVAL 1 MONTH    Off the top of my head  I can t think of an elegant way to get the first day of last month in MySQL  but this will certainly work   SELECT CONCAT LEFT NOW   - INTERVAL 1 MONTH 7   -01      Put them together and you get a query that solves your problem   SELECT   FROM your table WHERE t  gt   CONCAT LEFT NOW   - INTERVAL 1 MONTH 7   -01   AND t  lt   NOW   - INTERVAL 1 MONTH

User · Answer

SELECT   FROM table WHERE myDtate BETWEEN now         DATE SUB NOW         INTERVAL 1 MONTH

[mysql] MySQL select all rows from last month until (now() - 1 month), for comparative purposes

Examples related to mysql

Examples related to datetime