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

Re: [HACKERS] roundoff problem in time datatype

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] roundoff problem in time datatype
Date: 2005-10-14 02:54:30
Message-ID: 200510140254.j9E2sVK16102@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
I have written the attached patch which I think does what you suggested.
I found all the places where we disallowed 24:00:00, and make it valid,
including nabstime.c.
	
	test=>  select '24:00:00'::time(0);
	   time
	----------
	 24:00:00
	(1 row)
	
	test=>  select '24:00:01'::time(0);
	ERROR:  date/time field value out of range: "24:00:01"
	
---------------------------------------------------------------------------

Tom Lane wrote:
> Inserting into a time field with limited precision rounds off, which
> is good except for this case:
> 
> regression=# select '23:59:59.9'::time(0);
>    time   
> ----------
>  24:00:00
> (1 row)
> 
> This is bad because:
> 
> regression=# select '24:00:00'::time(0);
> ERROR:  date/time field value out of range: "24:00:00"
> 
> which means that data originally accepted will fail to dump and reload.
> 
> I see this behavior in all versions back to 7.3.  7.2 was even more
> broken:
> 
> regression=# select '23:59:59.9'::time(0);
>    time   
> ----------
>  00:00:00
> (1 row)
> 
> I think the correct behavior has to be to check for overflow again
> after rounding off.  Alternatively: why are we forbidding the value
> 24:00:00 anyway?  Is there a reason not to allow the hours field
> to exceed 23?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 

-- 
  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-14 02:59:26
Subject: Re: [HACKERS] roundoff problem in time datatype
Previous:From: Tom LaneDate: 2005-10-14 02:44:54
Subject: Re: Seeing context switch storm with 10/13 snapshot of 8.1beta3

pgsql-patches by date

Next:From: Tom LaneDate: 2005-10-14 02:59:26
Subject: Re: [HACKERS] roundoff problem in time datatype
Previous:From: Bruce MomjianDate: 2005-10-14 01:53:14
Subject: Re: BUG #1962: ECPG and VARCHAR

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