Date: Fri, 23 Feb 2007 10:24:27 -0600
From: "Jim C. Nasby" <jim@nasby.net>
To: Bruce Momjian <bruce@momjian.us>
Cc: Jim Nasby <jimn@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	gdavis@refractions.net, pgsql-sql@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] timestamp subtraction (was Re: formatting intervals
	with to_char)
Message-ID: <20070223162426.GI19527@nasby.net>
References: <DB91DDA2-F0F2-42CF-B5E8-A481E622F1CD@enterprisedb.com>
	<200702210107.l1L17Bm21179@momjian.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200702210107.l1L17Bm21179@momjian.us>
User-Agent: Mutt/1.5.13 (2006-08-11)

Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
return 25:00:00, not 1 day 1:00.

I agree with Tom that this should be changed; I'm just arguing that we
might well need a backwards-compatibility solution for a while. At the
very least we'd need to make this change very clear to users.

On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
> 
> One problem with removing justify_hours() is that this is going to
> return '24:00:00', rather than '1 day:
> 	
> 	test=> select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
> 	00:00:00'::timestamptz;
> 	 ?column?
> 	----------
> 	 24:00:00
> 	(1 row)
> 
> ---------------------------------------------------------------------------
> 
> Jim Nasby wrote:
> > On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> > > regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01  
> > > 09:30:41'::timestamp);
> > >      ?column?
> > > ------------------
> > >  14 days 14:28:19
> > > (1 row)
> > >
> > > should be reporting '350:28:19' instead.
> > >
> > > This is a hack that was done to minimize the changes in the regression
> > > test expected outputs when we changed type interval from months/ 
> > > seconds
> > > to months/days/seconds.  But I wonder whether it wasn't a dumb idea.
> > > It is certainly inconsistent, as noted in the code comments.
> > >
> > > I'm tempted to propose that we remove the justify_hours call, and tell
> > > anyone who really wants the old results to apply justify_hours() to  
> > > the
> > > subtraction result for themselves.  Not sure what the fallout would  
> > > be,
> > > though.
> > 
> > I suspect there's applications out there that are relying on that  
> > being nicely formated for display purposes.
> > 
> > I agree it should be removed, but we might need a form of backwards  
> > compatibility for a version or two...
> > --
> > Jim Nasby                                    jimn@enterprisedb.com
> > EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> > 
> >                http://www.postgresql.org/docs/faq
> 
> -- 
>   Bruce Momjian  <bruce@momjian.us>          http://momjian.us
>   EnterpriseDB                               http://www.enterprisedb.com
> 
>   + If your life is a hard drive, Christ can be your backup. +
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)