I want to select all rows since 20th last month.
I know I can select a date one month ago using SELECT NOW() - INTERVAL 1 MONTH, but how can I set a specific day in that month?
Today is March 28th, I want to select all rows newer than February 20th.
Even if I could set the day directly, if the queried date was 31st that wouldn't work for February, where instead it should select the last day of that month.
Note: 20th is just an example, the value is dynamically generated somewhere else in the code.
Some examples:
Day: 20th each month
Now: March 28th | Query: February 20th
Now: May 28th | Query: April 20th
Day: 31st each month
Now: March 28th | Query: February 28th // we select last day instead of 31st
Now: May 28th | Query: April 30th // we select last day instead of 31st
The full query would be something like SELECT * FROM sales WHERE date > SET_DAY(20, NOW() - INTERVAL 1 MONTH).
So, what can I replace that SET_DAY with (that also works with shorter months)?
A pretty common use case for this are billing periods, where you want to select all data from within the current billing cycle.
Assuming you have a parameter in the client that is the target day number, and you want to validate it in the sql (making sure you come up with a day inside the previous month), not in the client, I would do:
least(
date(date_format(current_date - interval 1 month, '%Y-%m-01')) + interval ? - 1 day,
date(date_format(current_date, '%Y-%m-01')) - interval 1 day
)
One caution: current_date will be the current date in the timezone of the connection, which is controlled by the client; I prefer to always be explicit if I know what timezone I want the day in, like:
date(convert_tz(utc_timestamp(), '+00:00', 'America/Los_Angeles'))
instead of just current_date (both places).