MySQL get specific date of last month-CodePudding

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.

CodePudding user response：

Well, I believe this solves the problem

select now()
- interval 1 month
- interval(
   day(now())
   - least(
      20,
      day(last_day(now() - interval 1 month))
     )
  ) day

CodePudding user response：

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).

CodePudding user response：

I just realized that my question didn't include all cases. If the last billing date is within this month, it should count the 20th of this month (not previous one).

Based on @ysth answer, I added an IF to select 20th of this month (if we are passed it), otherwise of previous month:

IF(DAY(current_date) >= 20, 
   date(date_format(current_date, '%Y-%m-01'))   interval 20 - 1 day,
    least(
        date(date_format(current_date - interval 1 month, '%Y-%m-01'))   interval 20 - 1 day,
        date(date_format(current_date, '%Y-%m-01')) - interval 1 day
    )
);