Re: BUG #13670: DST discrepancy between age() and subtraction for timestamptz arguments

>
> I think you both are fundamentally missing the point.


Yes, I clearly missed it. I didn't understand what was meant by "symbolic".
Maybe the docs could be clarified a bit; the words "imprecise" and "display
purposes" come to mind.

If that is indeed the purpose of age(), then I do agree that it is useful
to have both it and the subtraction operator.

Thank you!


John Pruitt
Delivery Director
Doozer Software, Inc.


On Mon, Oct 12, 2015 at 10:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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
>