From: | Sebastien FLAESCH <sf(at)4js(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org, mmoncure(at)gmail(dot)com, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: INTERVAL SECOND limited to 59 seconds? |
Date: | 2009-06-01 12:56:29 |
Message-ID: | 4A23CFFD.9050207@4js.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(at)4js(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Carrera | 2009-06-01 13:28:39 | ruby connect |
Previous Message | Grzegorz Jaśkiewicz | 2009-06-01 12:55:13 | Re: newbie table design question |
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Carrera | 2009-06-01 13:28:39 | ruby connect |
Previous Message | Sam Mason | 2009-06-01 09:19:36 | Re: Patch: AdjustIntervalForTypmod shouldn't discard high-order data |