Search Postgresql Archives

Re: INTERVAL SECOND limited to 59 seconds?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx> 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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux