Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals

Sorry, I should have CC'ed hackers on this. The issue is that because
of interval_justify_hours(), subtracting a fixed interval from a
timestamp and re-adding the same value produces a different result.

---------------------------------------------------------------------------

Bruce Momjian wrote:
>
> I saw a lot of disussion because I forgot to specify that my tests were
> for EST5EDT, but what about the use of interval_justify_hours() in
> timestamp_mi(). Is this something we want to change?
>
> ---------------------------------------------------------------------------
>
> Bruce Momjian wrote:
> > Klint Gore wrote:
> > > On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > > regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval;
> > > > ?column?
> > > > ------------------------
> > > > 2005-10-30 13:22:00-05
> > > > (1 row)
> > > >
> > > > regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz;
> > > > ?column?
> > > > ----------------
> > > > 1 day 01:00:00
> > > > (1 row)
> > > >
> > > > ISTM that given the former result, the latter calculation ought to
> > > > produce '1 day', not something else.
> > >
> > > Would the '1 day' result know it was 24 hours or be the new 23/24/25
> > > hour version of '1 day'?
> >
> > It has no idea. When you do a subtraction, it isn't clear if you are
> > interested in "days" or "hours", so we give hours. If you want days,
> > you should convert the timestamps to dates and just subtract them.
> >
> > > If it was the new version, could you get the original values back?
> > > i.e. what would be the result of
> > > select
> > > ('2005-10-29 13:22:00-04'::timestamptz +
> > > ('2005-10-30 13:22:00-05'::timestamptz -
> > > '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> >
> > You bring up a good point here. With current CVS your subtraction
> > yields:
> >
> > test-> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz);
> > ?column?
> > ----------------
> > 1 day 01:00:00
> > (1 row)
> >
> > so adding that to the first timestamp gets:
> >
> > test=> select
> > test-> ('2005-10-29 13:22:00-04'::timestamptz +
> > test(> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> > timezone
> > ---------------------
> > 2005-10-30 14:22:00
> > (1 row)
> >
> > This is certainly _not_ what someone would expect as a return value.
> > What happens is that we subtract to generate the number of hours
> > different, but then get all smart that "oh, that is one day to add, and
> > one hour" and return an unexpected value.
> >
> > This is actually a good argument that the use of
> > interval_justify_hours() in timestamp_mi() is a mistake. Without this
> > call, we have:
> >
> > test=> select
> > test-> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz);
> > ?column?
> > ----------
> > 25:00:00
> > (1 row)
> >
> > and
> >
> > test=> select
> > test-> ('2005-10-29 13:22:00-04'::timestamptz +
> > test(> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> > timezone
> > ---------------------
> > 2005-10-30 13:22:00
> > (1 row)
> >
> > but it also has the tendency to return some very high values for hours:
> >
> > test=> select
> > test-> ('2005-12-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz);
> > ?column?
> > ------------
> > 1489:00:00
> > (1 row)
> >
> > but again, if you want days, you can cast to days.
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073