Haribabu Kommi <kommi.haribabu@gmail.com> writes:
> On Tue, Oct 13, 2015 at 1:50 AM, John Pruitt <jpruitt@doozer.com> wrote:
>> Okay, I'll acknowledge that the issue has been discussed before, however I
>> fail to see how the issue can be considered resolved. The function takes
>> time zone aware arguments, ignores that input, and returns plainly incorrect
>> answers - the very definition of a bug.
> I also feel like that it is a bug. Here I attached a patch that
> corrects the problem.
I think you both are fundamentally missing the point.
The age() function exists to provide a "symbolic" difference between two
timestamps. In its intended use-case, the difference between midnight
on March 1 2015 and midnight on April 1 2015 is one month. Not one month
plus or minus an hour.
If you want the other behavior, why aren't you using plain timestamp
subtraction?
regression=# select age('1 april 2015'::timestamptz, '1 march 2015'::timestamptz);
age
-------
1 mon
(1 row)
regression=# select '1 april 2015'::timestamptz - '1 march 2015'::timestamptz;
?column?
------------------
30 days 23:00:00
(1 row)
(This is based on US DST rules, you might get different results in other
timezones.)
Even discounting any thought of backwards compatibility, it does not seem
to me that erasing the distinction between these behaviors is a good
thing. People might legitimately want either one.
Another point worth considering is this:
regression=# select '1 march 2015'::timestamptz + age('1 april 2015'::timestamptz, '1 march 2015'::timestamptz);
?column?
------------------------
2015-04-01 00:00:00-04
(1 row)
While I'm not sure that "X + age(Y, X) = Y" holds universally, it does
hold in this example, and the proposed patch would break that.
Having said that, I notice that the seemingly even more obvious
identity "X + (Y - X) = Y" doesn't work in this case. Maybe we should do
something about that, or maybe not. My point is mainly that there are
a *lot* of moving parts in this area, as well as a considerable amount
of backwards-compatibility history that we must not take lightly. It is
well to remember also that civil time and DST laws were written by
politicians who have never heard of mathematical consistency.
regards, tom lane