Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Fuhr <mike(at)fuhr(dot)org>,Michael Glaesemann <grzm(at)myrealbox(dot)com>,Russell Smith <mr-russ(at)pws(dot)com(dot)au>, pgsql-bugs(at)postgresql(dot)org,Nicholas <hb(at)x256(dot)com>
Subject: Re: BUG #1993: Adding/subtracting negative time intervals
Date: 2005-10-26 03:41:54
Message-ID: 200510260341.j9Q3fst23599@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-10-26 03:55:44
Subject: Re: add_missing_from breaks existing views
Previous:From: Tom LaneDate: 2005-10-26 03:38:56
Subject: Re: add_missing_from breaks existing views

pgsql-bugs by date

Next:From: Klint GoreDate: 2005-10-26 04:23:20
Subject: Re: BUG #1993: Adding/subtracting negative time intervals
Previous:From: Bruce MomjianDate: 2005-10-26 03:17:05
Subject: Re: BUG #1993: Adding/subtracting negative time intervals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group