Re: BUG #1993: Adding/subtracting negative time intervals

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