Re: BUG #1993: Adding/subtracting negative time intervals - Mailing list pgsql-bugs

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