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: Bugs for PostgreSQL <pgsql-bugs(at)postgreSQL(dot)org>
Cc: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>, 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>, Nicholas <hb(at)x256(dot)com>
Subject: Re: BUG #1993: Adding/subtracting negative time intervals
Date: 2005-10-26 13:03:01
Message-ID: 200510261303.j9QD31u19845@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-10-26 13:59:33
Subject: Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Previous:From: Bruce MomjianDate: 2005-10-26 12:52:55
Subject: Re: PQescapeIdentifier

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2005-10-26 13:54:48
Subject: Re: [BUGS] Bug#333854: pg_group file update problems
Previous:From: Ludmil TinkovDate: 2005-10-26 12:47:47
Subject: Re: BUG #1996: DISTINCT fails with national character varying

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