Notifications
Sign Up Sign In
Q&A

How long in days is a MONTH in MySQL?

+3
−0

If one finds where the past number of months equals the past number of days like this,

select distinct DATE_SUB(now(), INTERVAL 92 DAY),DATE_SUB(now(), INTERVAL 3 MONTH)

The numbers that are currently returned are,

  • 1 month = 31 days
  • 2 month = 62 days
  • 3 month = 92 days
  • 4 month = 123 days
  • 5 month = 153 days
  • 6 month = 184 days

Is MySQL rounding the number of days or is it taking into account the number of days in a particular month?

Why should this post be closed?

0 comments

1 answer

+7
−0

There is no fixed number of days in a MONTH interval. DATE_SUB is mostly just decrementing the number in the months position of the date provided. So DATE_SUB('2020-09-14', INTERVAL 3 MONTH) is '2020-06-14' and DATE_SUB('2020-03-14', INTERVAL 3 MONTH) is '2019-12-14'. The documentation describes what happens in edge cases like DATE_SUB('2020-10-31', INTERVAL 1 MONTH):

If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month

So the result of DATE_SUB('2020-10-31', INTERVAL 1 MONTH) would be adjusted to '2020-09-30'.

1 comment

So the quick answer is: a MySQL month is basically as long as a human month (edge cases might be debatable). ‭Alexei‭ 4 days ago

Sign up to answer this question »