MySQL select all rows from last month until (now() – 1 month), for comparative purposes

by

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 >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) and
  /* Smaller or equal than one month ago */
  t.date <= DATE_SUB(NOW(), INTERVAL 1 MONTH)

More Related Contents:

  1. Should I use the datetime or timestamp data type in MySQL?
  2. How do you set a default value for a MySQL Datetime column?
  3. MySQL Query GROUP BY day / month / year
  4. Error in MySQL when setting default value for DATE or DATETIME
  5. How to group by week in MySQL?
  6. Difference between two dates in MySQL
  7. MySQL Query to select data from last week?
  8. MySQL convert date string to Unix timestamp
  9. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  10. Timestamp with a millisecond precision: How to save them in MySQL
  11. How to store a datetime in MySQL with timezone info
  12. How to select date from datetime column?
  13. Datetime equal or greater than today in MySQL
  14. Set NOW() as Default Value for datetime datatype?
  15. Group mysql query by 15 min intervals
  16. SQL Query to show nearest date?
  17. How to subtract hours from a datetime in MySQL?
  18. How to convert time to the time zone of the iPhone device?
  19. Select data between a date/time range
  20. mysql: get record count between two date-time
  21. MySQL compare DATE string with string from DATETIME field
  22. MySql difference between two timestamps in days?
  23. How to cast DATETIME as a DATE in mysql?
  24. MySQL – select data from database between two dates
  25. how to cast the hexadecimal to varchar(datetime)?
  26. How to compare timestamp dates with date-only parameter in MySQL?
  27. how to partition a table by datetime column?
  28. Get total hours worked in a day mysql
  29. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment