Search code examples
mysqldocumentation

MySQL 8.0 Reference says MOD() + 1 returns different result from 1 + MOD()

I am trying to learn MySQL reading Official MySQL 8.0 reference/tutorial. https://dev.mysql.com/doc/refman/8.0/en/date-calculations.html

In the paragraph that explains how you get the January using calculation, it says

A different way to accomplish the same task is to add 1 to get the next month after the current one after using the modulo function (MOD) to wrap the month value to 0 if it is currently 12:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

My question is why the addition should be after MOD()? I get the same result even if the addition is before 'MOD(MONTH(CURDATE()), 12)' as below.

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 1 + MOD(MONTH(CURDATE()), 12);

am I missing something here?

Solution

  • The following two expressions are identical, because addition is, by definition, commutative:

    MOD(MONTH(CURDATE()), 12) + 1
1 + MOD(MONTH(CURDATE()), 12)

    The logic of why either of these works is that when the month of the current date, which is a number between 1 and 12, be 11 or less, the modulus by 12 simply returns that number, and we add one month to it. In the case of a December month (12), the expression 12 % 12 returns 0, and then we add one to obtain January (1).