To: Sebastien FLAESCH <sf@4js.com>
cc: pgsql-general@postgresql.org, mmoncure@gmail.com,
	pgsql-hackers@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds? 
In-reply-to: <4A127038.3010103@4js.com> 
References: <4A127038.3010103@4js.com>
Comments: In-reply-to Sebastien FLAESCH <sf@4js.com>
	message dated "Tue, 19 May 2009 10:39:20 +0200"
Date: Sun, 31 May 2009 17:35:33 -0400
Message-ID: <15585.1243805733@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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