You might be aware of theintervaldata type, which allows mathematicaloperationson date, time, and timestamp values. This can lead to odd behavior, but thisemail postingshowed me a new oddity when dealing with months containing a different number of days. First, let's summarize how many days are in the first five months of 2017:
It seems that once the calculation lands on a lower-numbered day of a month, because the month doesn't have as many days as the previous stop, it never advances past that day number, even if later months have more days. Let's be more overt about this:
All three queries produce different results. This shows that addition with such values is neitherassociative(queries one and two) norcommutative(queries one and three). Real number addition is both associative and commutative, so it is confusing that addition with intervals is not for some values, though it is for others. (Other interval operations have similar issues, e.g. subtraction.)
While the results are surprising, it is unclear how Postgres could do any better without adding some very complex logic — so, developers beware.