Re: INTERVAL SECOND limited to 59 seconds? - Mailing list pgsql-general

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
>