From: | John Pruitt <jpruitt(at)doozer(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #13670: DST discrepancy between age() and subtraction for timestamptz arguments |
Date: | 2015-10-14 14:24:39 |
Message-ID: | CACune3wY4EfK4RDmEdYZYzx1DiC5QTFBV2fPCPtAt+S_yzq=OQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>
> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> writes:
> > On Tue, Oct 13, 2015 at 1:50 AM, John Pruitt <jpruitt(at)doozer(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-10-14 14:25:38 | Re: BUG #13679: Planer chooses not optimal plan |
Previous Message | John McKown | 2015-10-14 13:50:19 | Re: BUG #13676: C typedef code generated by ecpg with wrong syntax |