Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Comments on How long in days is a MONTH in MySQL?
Parent
How long in days is a MONTH in MySQL?
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?
Post
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, orYEARand 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'.
0 comment threads