Thread: INTERVAL type: SQL92 implementation
If full SQL92 implementation of INTERVAL would be a welcome addition,
could it be added as a TODO item? I would like to work on it, since I
want to use some features that are not currently supported.
SQL92 INTERVAL data type (also SQL99, I think):
<interval type> ::= INTERVAL {{<start field> TO <end field>} | <single datetime field>}
<start field> ::= <non-second datetime field> [(<interval leading field precision>)]
<end field> ::= <non second datetime field> | SECOND [(<fractional seconds precision>)]
<single datetime field> ::= <non-second datetime field> [(<interval leading field
precision>)]| SECOND[(<interval leading field precision>
[,<fractionalseconds precision>])]
<non-second datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE
0 < <interval leading field precision> < implementation defined maximum (default is 2)
0 <= <fractional seconds precision> < 10 (default is 6)
INTERVALs may be defined by a range within either YEAR TO MONTH or DAY TO SECOND.
INTERVAL literals are defined as:
INTERVAL [+|-]'<value string>' <interval qualifier>
<interval qualifier> ::= <start field> [TO <end field>]
Part of this syntax is supported by the parser, but not consistently.
Valid SQL92 syntax that is not currently supported:
junk=# SELECT INTERVAL '1990' YEAR(4); ERROR: parser: parse error at or near "(" junk=# select INTERVAL '1990' YEAR;
ERROR: Bad interval external representation '1990' junk=# SELECT INTERVAL -'1-1' YEAR TO MONTH; ERROR: parser: parse
errorat or near "YEAR" junk=# SELECT INTERVAL +'100 0:0:0.1' DAY(3) TO SECOND; ERROR: parser: parse error at or near
"DAY"junk=# SELECT INTERVAL +'100 0:0:0.1' DAY TO SECOND; ERROR: parser: parse error at or near "DAY" junk=# --
actually,it doesn't like the + junk=# SELECT INTERVAL '0:0:0.0:' HOUR TO SECOND(9); ERROR: parser: parse error at or
near"(" junk=# SELECT INTERVAL '100000.001' SECOND(6,3); ERROR: parser: parse error at or near "(" junk=# SELECT
INTERVAL'100000.001' SECOND; ?column? ------------------- 1 day 03:46:40.00 (1 row)
junk=# -- should output '100000.001' junk=# SELECT INTERVAL -'10' MINUTE; ERROR: parser: parse error at or near
"MINUTE"junk=# SELECT INTERVAL '1:1' HOUR(6) TO MINUTE; ERROR: parser: parse error at or near "("
Valid interval value format not currently supported:
year-month
Since there are aspects of SQL92 interval representation that clash
with the current implementation, I would suggest that current
practice be followed unless SQL92 syntax is used. So a field that
is of type INTERVAL without qualification would continue to work
as it does now (except that I would like to implement range checking).
The main difference would be in the output format. For a
SQL92-compliant interval column, the output would be the appropriate
parts of either
year-month
or
day hour:minute:second.fractional_second
according to the field definition, without any words (i.e.: "1 03:46:40.00"
instead of "1 day 03:46:40.00", and "3-5" instead of "3 years 5 mons").
All parts within the range will be shown, even if they are trailing zeros.
The other difference would be that input values would be range-checked
to see that they didn't exceed the possible range of the type; so
the range of INTERVAL HOUR(3) TO MINUTE would be 0 seconds to
+|-999:59:59.999999 and inserting a value outside the range would be
an error. Intervals of the current type also need range-checking:
junk=# select interval '199999999 years'; ?column?
---------------------------157913942 years -4 mons
(1 row)
What do you think?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
======================================== "Use hospitality one to another without grudging."
I Peter 4:9
> If full SQL92 implementation of INTERVAL would be a welcome addition,
> could it be added as a TODO item? I would like to work on it, since I
> want to use some features that are not currently supported.
...
> Valid SQL92 syntax that is not currently supported:
...
> junk=# SELECT INTERVAL '1990' YEAR(4);
...
So far, I've had shift/reduce troubles trying to have a trailing
qualifier field like this.
> junk=# SELECT INTERVAL -'1-1' YEAR TO MONTH;
> ERROR: parser: parse error at or near "YEAR"
A leading sign in front of a string-like field? Yuck.
...
> Valid interval value format not currently supported:
> year-month
I'll look at accepting this for the current INTERVAL type too.
> Since there are aspects of SQL92 interval representation that clash
> with the current implementation, I would suggest that current
> practice be followed unless SQL92 syntax is used. So a field that
> is of type INTERVAL without qualification would continue to work
> as it does now (except that I would like to implement range checking).
I like this point. Really, SQL99 intervals are a bit unwieldy, though
they do have "extra features" which someone might find useful.
> The main difference would be in the output format...
> ... parts of either year-month or
> day hour:minute:second.fractional_second
We could probably support this format (now that you have described it to
us) at least for the "SQL" datestyle even for the existing INTERVAL
type.
> according to the field definition, without any words (i.e.: "1 03:46:40.00"
> instead of "1 day 03:46:40.00", and "3-5" instead of "3 years 5 mons").
> All parts within the range will be shown, even if they are trailing zeros.
This set of conventions might let the date/time parser do a complete
job. I put in the "days" text label to reduce the ambiguity of a single,
unlabeled integer.
> What do you think?
Have you gotten started yet? Finished yet?? ;)
- Thomas
(back on list)
> As far as I can see, it is the same. My examples come from Cannan and Otten
> on SQL92, but I read the spec for SQL99 and I can't see any obvious
> change, except that INTERVAL YEAR TO YEAR (and any other X TO X) is no
> longer allowed. (I take it you have a copy of SQL99?)
We have a copy of an SQL99 draft which seems to be reasonably complete.
afaik we haven't come across an actual released version. Let me know if
you want me to forward it; perhaps it is on the ftp or web site?
> >o We need to figure out how to parse it in gram.y. I looked at it a
> >little bit (a couple of hours?) and it was not obvious how to get rid of
> >shift/reduce problems.
> I don't have any deep knowledge of yacc/bison...yet.
Oh, you will... ;)
> I feel unhappy about multiplying interval types like that. I would rather
> restrict it to interval (as now), intervalym (YEAR TO MONTH) and intervalds
> (DAY TO SECOND), with the parameters determining the interval range.
But that means (perhaps?) that you can't define a column INTERVAL DAY,
since internally everything would accept all values DAY TO SECOND. I
know you proposed setting an internal mask, but that would be per-value,
not per-column, so it doesn't help. The attribute system may not be much
help here either, unless we somehow generalize it (to allow types to
keep their own impure storage?).
> otherwise we would have 13 new types and would need to make conversion
> functions for all of them. SQL99 says that YEAR TO MONTH and DAY TO SECOND
> are incompatible; the results of other combinations give the combined
> maximum range: DAY TO HOUR + HOUR TO SECOND = DAY TO SECOND, but I don't
> see this as being outside the capabilities of the 2 new types I propose.
> Is there some reason in the internals why it would be necessary to create all
> 13 new types?
3 for YEAR/MONTH, and 10 for DAY/HOUR/MIN/SEC to get all the
combinations. If you convert to a "super interval" for internal
operations, then you may only need the I/O and conversion functions,
which would be easy.
My example still holds as a test case to evaluate an implementation
afaik:
create table t (id interval day); insert into t(id) select interval '2' day + interval '05' minute;
will need to be stored with only the day field non-zero. Certainly that
column can not be allowed to end up holding quantities other than
integral days, right?
Also, the column defined above has no ability to enforce the "day only"
character of the column if we are using only a single type and without
help from the type or attribute system already in place.
> As I said above, I feel that this is to over-complicate things...
Hmm, but it may be a required minimum level of complication to meet the
spec. Given the arcane syntax and limited functionality (note the
gratuitous editorializing ;) it probably isn't worth doing unless it
gets us on an obvious path to SQL99-compliant functionality.
Also, it is one of the edge cases for SQL99, so even if it is a pain to
do we are only doing it once. They couldn't possibly come up with
anything uglier for SQL0x, could they? Please say no...
...
> the distinction between YEAR TO MONTH and DAY TO SECOND is one that is
> present in the existing interval type, so perhaps we could even get away with
> only one new type?
Not sure what you mean here. The existing type does keep years/months
stored separately from the days/hours/minutes/seconds (a total of two
internal fields) but SQL99 asks that these be kept completely away from
each other from what you've said. Does it define any arithmetic between
the two kinds of intervals?
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> wrote: > We have a copy of an SQL99 draft which seems to be reasonably complete. > afaik we haven't come across an actual released version. Let me know if > you want me to forward it; perhaps it is on the ftp or web site? ftp://ftp.postgresql.org/pub/doc/sql/sql1998.tar.gz Mostly the same files are at http://gatekeeper.research.compaq.com/pub/standards/sql/ (or ftp). I didn't know until recently that the ANSI standard was available in PDF form for an almost reasonable price ($18/part) compared to the outrageous ISO price ($98 to $275 per part). See http://webstore.ansi.org/ansidocstore/find.asp?find_spec=sql [...] > Not sure what you mean here. The existing type does keep years/months > stored separately from the days/hours/minutes/seconds (a total of two > internal fields) but SQL99 asks that these be kept completely away from > each other from what you've said. Does it define any arithmetic between > the two kinds of intervals? No. Days/hours/minutes/seconds are exact quantities whereas years and months are not, so they don't mix.
I seem to have the complete released (I think) SQL99 docs. If anyone wants them - just reply to me personally. Should they be put on the postgres site? Is that legal? Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Ken Hirsch > Sent: Sunday, 2 September 2001 5:18 AM > To: Hackers List > Subject: Re: [HACKERS] INTERVAL type: SQL92 implementation > > > > Thomas Lockhart <lockhart@fourpalms.org> wrote: > > We have a copy of an SQL99 draft which seems to be reasonably complete. > > afaik we haven't come across an actual released version. Let me know if > > you want me to forward it; perhaps it is on the ftp or web site? > > ftp://ftp.postgresql.org/pub/doc/sql/sql1998.tar.gz > > Mostly the same files are at > http://gatekeeper.research.compaq.com/pub/standards/sql/ > (or ftp). > > I didn't know until recently that the ANSI standard was available in PDF > form for an almost reasonable price ($18/part) compared to the outrageous > ISO price ($98 to $275 per part). > > See http://webstore.ansi.org/ansidocstore/find.asp?find_spec=sql > > [...] > > Not sure what you mean here. The existing type does keep years/months > > stored separately from the days/hours/minutes/seconds (a total of two > > internal fields) but SQL99 asks that these be kept completely away from > > each other from what you've said. Does it define any arithmetic between > > the two kinds of intervals? > > No. Days/hours/minutes/seconds are exact quantities whereas years and > months are not, so they don't mix. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Good day, Sorry to post to this list about a patch, but I seem to be having some difficult getting on the pgsql-patches list; keep getting an "illegal command" when I send it "subscribe", for some reason. At any rate: In documenting the to_char() function for transformation of numbers to text, I noticed that the "RN" template character sequence was displaying some unusual behavior; specifically, unless in fill mode (with the "FM" sequence), it would either return the result of the last query executed derived from a to_char() result, or what appears to be a garbage pointer if there was no such last query. Example output from PostgreSQL 7.1.3: ------------------------------------------------------- lx=# SELECT to_char(485, 'RN'); to_char -----------------UERY :command 1 (1 row) lx=# SELECT to_char(485, 'FMRN');to_char ---------CDLXXXV (1 row) lx=# SELECT to_char(485, 'RN');to_char ---------CDLXXXV (1 row) lx=# SELECT to_char(1000, 'RN');to_char ---------CDLXXXV (1 row) lx=# SELECT 1, 2, to_char(900, '999');?column? | ?column? | to_char ----------+----------+--------- 1 | 2 | 900 (1 row) lx=# SELECT to_char(485, 'RN');to_char --------- 900 (1 row) ------------------------------------------------------- Upon looking into src/backend/utils/adt/formatting.c, I noticed that for RN transforms: strcpy(Np->inout_p, Np->number_p); was only being called within the IS_FILLMODE if block. Moving it out, and above that check seems to correct this behavior, and I've attached Patches for both today's pgsql CVS snapshot and postgresql-7.1.3. Both compile, but I've only tested the latter since my data path is not setup for pre-7.2, and it seems like a fairly small change. I consider myself a competent programmer, but never having hacked on Postgres, I'm not 100% sure that this modification is totally correct (e.g., if there are any strange side-effects from doing this?), since I'm not even sure what the Np pointers are achieving in this instance. ;) I'm guessing its copying the actual output result into the output value's char* pointer, as that would explain the garbage pointer if it was never copied. Any explanation would be greatly appreciated, as I'd like to document this apparent bug correctly. Regards, Jw. -- jlx@commandprompt.com - John Worsley @ Command Prompt, Inc. by way of pgsql-hackers@commandprompt.com
On Fri, Aug 31, 2001 at 07:28:50PM -0700, Command Prompt, Inc. wrote: > In documenting the to_char() function for transformation of numbers to > text, I noticed that the "RN" template character sequence was displaying > some unusual behavior; specifically, unless in fill mode (with the "FM" > sequence), it would either return the result of the last query executed > derived from a to_char() result, or what appears to be a garbage pointer > if there was no such last query. You are right it's bug. For the 'RM' in non-fillmode is to_char() quiet. I will fix it for 7.2. > I consider myself a competent programmer, but never having hacked on > Postgres, I'm not 100% sure that this modification is totally correct I check it and if it's good solution we use it. Thanks! Karel PS. Bruce, please, can you apply my previous (31 Aug) patch with to_char() stuff? I want fix this bug in really actualCVS code. Thanks. -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
On Tue, Sep 04, 2001 at 11:37:48AM -0400, Bruce Momjian wrote: > > On Fri, Aug 31, 2001 at 07:28:50PM -0700, Command Prompt, Inc. wrote: > > > > > In documenting the to_char() function for transformation of numbers to > > > text, I noticed that the "RN" template character sequence was displaying > > > some unusual behavior; specifically, unless in fill mode (with the "FM" > > > sequence), it would either return the result of the last query executed > > > derived from a to_char() result, or what appears to be a garbage pointer > > > if there was no such last query. > > > > You are right it's bug. For the 'RM' in non-fillmode is to_char() quiet. > > I will fix it for 7.2. > > Karel, I assume you will send in a patch yourself, right? Right. It needs check. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
> On Fri, Aug 31, 2001 at 07:28:50PM -0700, Command Prompt, Inc. wrote: > > > In documenting the to_char() function for transformation of numbers to > > text, I noticed that the "RN" template character sequence was displaying > > some unusual behavior; specifically, unless in fill mode (with the "FM" > > sequence), it would either return the result of the last query executed > > derived from a to_char() result, or what appears to be a garbage pointer > > if there was no such last query. > > You are right it's bug. For the 'RM' in non-fillmode is to_char() quiet. > I will fix it for 7.2. Karel, I assume you will send in a patch yourself, right? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
I have checked this in CVS and it is working fine. Karel, have you fixed this? I can't find a place where I have applied a fix for this. > Good day, > > Sorry to post to this list about a patch, but I seem to be having some > difficult getting on the pgsql-patches list; keep getting an "illegal > command" when I send it "subscribe", for some reason. At any rate: > > In documenting the to_char() function for transformation of numbers to > text, I noticed that the "RN" template character sequence was displaying > some unusual behavior; specifically, unless in fill mode (with the "FM" > sequence), it would either return the result of the last query executed > derived from a to_char() result, or what appears to be a garbage pointer > if there was no such last query. > > Example output from PostgreSQL 7.1.3: > ------------------------------------------------------- > lx=# SELECT to_char(485, 'RN'); > to_char > ----------------- > UERY :command 1 > (1 row) > > lx=# SELECT to_char(485, 'FMRN'); > to_char > --------- > CDLXXXV > (1 row) > > lx=# SELECT to_char(485, 'RN'); > to_char > --------- > CDLXXXV > (1 row) > > lx=# SELECT to_char(1000, 'RN'); > to_char > --------- > CDLXXXV > (1 row) > > lx=# SELECT 1, 2, to_char(900, '999'); > ?column? | ?column? | to_char > ----------+----------+--------- > 1 | 2 | 900 > (1 row) > > lx=# SELECT to_char(485, 'RN'); > to_char > --------- > 900 > (1 row) > ------------------------------------------------------- > > Upon looking into src/backend/utils/adt/formatting.c, I noticed that for > RN transforms: > > strcpy(Np->inout_p, Np->number_p); > > was only being called within the IS_FILLMODE if block. Moving it out, and > above that check seems to correct this behavior, and I've attached Patches > for both today's pgsql CVS snapshot and postgresql-7.1.3. Both compile, > but I've only tested the latter since my data path is not setup for > pre-7.2, and it seems like a fairly small change. > > I consider myself a competent programmer, but never having hacked on > Postgres, I'm not 100% sure that this modification is totally correct > (e.g., if there are any strange side-effects from doing this?), since I'm > not even sure what the Np pointers are achieving in this instance. ;) I'm > guessing its copying the actual output result into the output value's > char* pointer, as that would explain the garbage pointer if it was never > copied. > > Any explanation would be greatly appreciated, as I'd like to document this > apparent bug correctly. > > > Regards, > Jw. > -- > jlx@commandprompt.com - John Worsley @ Command Prompt, Inc. > by way of pgsql-hackers@commandprompt.com Content-Description: [ Attachment, skipping... ] Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026