agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Sebastien FLAESCH <sf@4js.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Cc: mmoncure@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
Date: Mon, 01 Jun 2009 14:56:29 +0200
Message-ID: <4A23CFFD.9050207@4js.com> (raw)
In-Reply-To: <15585.1243805733@sss.pgh.pa.us>
References: <4A127038.3010103@4js.com>
<15585.1243805733@sss.pgh.pa.us>
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
>
view thread (10+ messages) latest in thread
Message-ID: <4A23CFFD.9050207@4js.com>
Permalink: ../4A23CFFD.9050207@4js.com/
Also on: postgresql.org/message-id/4A23CFFD.9050207@4js.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sf@4js.com, tgl@sss.pgh.pa.us, pgsql-general@postgresql.org, mmoncure@gmail.com
Subject: Re: INTERVAL SECOND limited to 59 seconds?
In-Reply-To: <4A23CFFD.9050207@4js.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox