You might be aware of the interval data type, which allows mathematical operations on date, time, and timestamp values. This can lead to odd behavior, but this email posting showed 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:
2017-01 31
2017-02 28
2017-03 31
2017-04 30
2017-05 31
Let's add four months to the last day of January, 2017 in two different ways:
SELECT '2017-01-31'::date + '4 month'::interval;
?column?
---------------------
2017-05-31 00:00:00
SELECT '2017-01-31'::date + ('1 month'::interval + '3 month'::interval);
?column?
---------------------
2017-05-31 00:00:00
Looks good — the results are the same, and they make sense. But look what happens when the calculation take a stop-over in February (operations are performed left to right):
SELECT '2014-01-31'::date + '1 month'::interval + '3 month'::interval;
?column?
---------------------
2014-05-28 00:00:00
and a stop-over in April:
SELECT '2014-01-31'::date + '3 month'::interval + '1 month'::interval;
?column?
---------------------
2014-05-30 00:00:00
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:
SELECT ('2014-01-31'::date + '1 month'::interval) + '3 month'::interval;
?column?
---------------------
2014-05-28 00:00:00
SELECT '2014-01-31'::date + ('1 month'::interval + '3 month'::interval);
?column?
---------------------
2014-05-31 00:00:00
SELECT ('2014-01-31'::date + '3 month'::interval) + '1 month'::interval;
?column?
---------------------
2014-05-30 00:00:00
All three queries produce different results. This shows that addition with such values is neither associative (queries one and two) nor commutative (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.
Bruce Momjian is a Senior Database Architect at EnterpriseDB.
This post originally appeared on Bruce's personal blog.