MySQL select complete last month

Quite often I need to query MySQL tables for a subset of data in relation to the current date, e.g. like this one

Give me all transactions for the last month.

Coming from a Rails background one is inclined to write something corresponding to:

Test.where(created_at:
  1.month.ago.beginning_of_month..1.month.ago.end_of_month)

Well, translating this to raw SQL is not going to be super hard, except that MySQL (even 22 years after its invention) is lacking convenient date handling.

1.month.ago                    => DATE_SUB(NOW(), INTERVAL 1 MONTH))
1.month.ago.end_of_month       => LAST_DAY( … )
1.month.ago.beginning_of_month => ?

MySQL does not have a builtin FIRST_DAY function, so this has to be emulated by adding 1 day to the LAST_DAY of the month before last.

DATE_ADD(
  LAST_DAY(
    DATE_SUB(NOW(), INTERVAL 2 MONTH)
  ),
  INTERVAL 1 DAY
)

Given the following test data in table test

id    amount    created_at (datetime)
1     300       2016-09-01 22:20:28
2     200       2016-09-15 22:20:49
3     100       2016-09-30 22:21:03
4     100       2016-10-04 22:22:29

we can now query the table to return all rows created last month

SELECT 
  *
FROM 
  test
WHERE
  DATE(created_at) BETWEEN
    DATE_ADD(
      LAST_DAY(
        DATE_SUB(
          CURDATE(), INTERVAL 2 MONTH
        )
      ), 
      INTERVAL 1 DAY
    )
    AND
    LAST_DAY(
      DATE_SUB(
        CURDATE(), INTERVAL 1 MONTH
      )
    )

which gives us the expected results (run in October 2016)

id    amount    created_at (datetime)
1     300       2016-09-01 22:20:28
2     200       2016-09-15 22:20:49
3     100       2016-09-30 22:21:03

Don’t forget to cast your DATETIME column to DATE in the where clause, otherwise MySQL preserves your current time when building the query and you will lose rows in the result set.

As we all know, there is more than one way to skin a cat. Using DATE_FORMAT and string interpolation are other ways of tackling the same problem.