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

Re: [HACKERS] timestamp subtraction (was Re: formatting intervals with to_char)

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
Cc: Jim Nasby <jimn(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, gdavis(at)refractions(dot)net, pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] timestamp subtraction (was Re: formatting intervals with to_char)
Date: 2007-04-02 22:46:15
Message-ID: 200704022246.l32MkFI13247@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Added to TODO:

        o Have timestamp subtraction not call justify_hours()?

          http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php


---------------------------------------------------------------------------

Jim C. Nasby wrote:
> 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(at)enterprisedb(dot)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(at)momjian(dot)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(at)nasby(dot)net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>        message can get through to the mailing list cleanly

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-hackers by date

Next:From: Chris BrowneDate: 2007-04-02 22:52:50
Subject: Re: Modifying TOAST thresholds
Previous:From: Tom LaneDate: 2007-04-02 22:37:11
Subject: Re: Bug in UTF8-Validation Code?

pgsql-sql by date

Next:From: Daniel CAUNEDate: 2007-04-02 23:08:44
Subject: Re: Calling void functions
Previous:From: ezequiasDate: 2007-04-02 18:13:58
Subject: Maxusers MaxConnections

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