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@sss.pgh.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073