Message-ID: <4A23CFFD.9050207@4js.com>
Date: Mon, 01 Jun 2009 14:56:29 +0200
From: Sebastien FLAESCH <sf@4js.com>
Organization: Four J's Development Tools
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-general@postgresql.org, mmoncure@gmail.com,
	pgsql-hackers@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
References: <4A127038.3010103@4js.com> <15585.1243805733@sss.pgh.pa.us>
In-Reply-To: <15585.1243805733@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thank you Tom for looking at this.

I would be pleased to help on testing the fix when available.

My plan is to store Informix INTERVALs (coming from the 4gl applications we
support) into PostgreSQL INTERVALs, and I have a bunch of tests for that...

I believe Informix INTERVALs (and related operators and functions) are not
100% SQL99, but they are close...

Thanks a lot!
Seb

Tom Lane wrote:
> Sebastien FLAESCH <sf@4js.com> writes:
>> I would expect that an INTERVAL SECOND can store more that 59 seconds.
> 
> I took a look into the SQL spec and I think that we do indeed have a
> spec compliance issue here.  SQL99 section 4.7 saith
> 
>          Within a value of type interval, the first field is constrained
>          only by the <interval leading field precision> of the associated
>          <interval qualifier>. Table 8, "Valid values for fields in INTERVAL
>          values", specifies the constraints on subsequent field values.
> 	 [ Table 8 says about what you'd expect, eg 0..23 for HOUR ]
>          Values in interval fields other than SECOND are integers and have
>          precision 2 when not the first field. SECOND, however, can be
>          defined to have an <interval fractional seconds precision> that
>          indicates the number of decimal digits maintained following the
>          decimal point in the seconds value. When not the first field,
>          SECOND has a precision of 2 places before the decimal point.
> 
> So in other words, "999 seconds" is a valid value for a field of type
> INTERVAL SECOND, *and should come out the same way*, not as "00:16:39",
> and certainly not as "00:00:39".
> 
> It might be a relatively easy fix to not truncate the input value
> incorrectly.  I haven't looked, but I think we should look now, because
> 8.4 has already changed the behavior in this area and it would be good
> not to change it twice.  The focus of the 8.4 work was to make sure that
> we would correctly interpret the values of spec-compliant interval
> literals, but this example shows we are not there yet.
> 
> We are fairly far away from being able to make it print out as the spec
> would suggest, because interval_out simply doesn't have access to the
> information that the field is constrained to be INTERVAL SECOND rather
> than some other kind of interval.  We also have got no concept at all of
> <interval leading field precision>, only of <interval fractional seconds
> precision>, so constraining the leading field to only a certain number
> of integral digits isn't possible either.  I don't foresee anything
> getting done about either of those points for 8.4.
> 
> 			regards, tom lane
>