Thread: Re: [HACKERS] Are we losing momentum?
> > That's a pretty reasonable thought. I work for a shop that sells
> > Postgres support, and even we install MySQL for the Q&D ticket
> > tracking system we recommend because we can't justify the cost to
> > port it to postgres. If the postgres support were there, we would
> > surely be using it.
>
> > How to fix such a situation, I'm not sure. "MySQL Compatability
> > Mode," anyone? :-)
>
> What issues are creating a compatibility problem for you?
I don't think these should be hacked into the backend/libpq, but I
think it'd be a huge win to hack in "show *" support into psql for
MySQL users so they can type:
SHOW (databases|tables|views|functions|triggers|schemas);
I have yet to meet a MySQL user who understands the concept of system
catalogs even though it's just the 'mysql' database (this irritates me
enough as is)... gah, f- it: mysql users be damned, I have three
developers that think that postgresql is too hard to use because they
can't remember "\d [table name]" and I'm tired of hearing them bitch
when I push using PostgreSQL instead of MySQL. I have better things
to do with my time than convert their output to PostgreSQL. Here goes
nothing...
I've tainted psql and added a MySQL command compatibility layer for
the family of SHOW commands (psql [-m | --mysql]).
The attached patch does a few things:
1) Implements quite a number of SHOW commands (AGGREGATES, CASTS,
CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES,
DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS,
PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS,
TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS)
SHOW thing
SHOW thing LIKE pattern
SHOW thing FROM pattern
SHOW HELP ON (topic || ALL);
etc.
Some of these don't have \ command eqiv's. :( I was tempted to add
them, but opted not to for now, but it'd certainly be a nice to
have.
2) Implements the necessary tab completion for the SHOW commands for
the tab happy newbies/folks out there. psql is more friendly than
mysql's CLI now in terms of tab completion for the show commands.
3) Few trailing whitespace characters were nuked
4) guc.c is now in sync with the list of available variables used for
tab completion
Few things to note:
1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in
this regard and that it should be INDEXES to be plural along with
the rest of the types, but INDEX is preserved for compatibility.
2) There are two bugs that I have yet to address
1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does
2) "SHOW [variable_of_choice];" doesn't work, but "SHOW
[variable_of_choice]\n;" does work... not sure where this
problem is coming from
3) I think psql is more usable as a result of this more verbose
syntax, but it's not the prettiest thing on the planet (wrote a
small parser outside of the backend or libraries: I don't want to
get those dirty with MySQL's filth).
4) In an attempt to wean people over to PostgreSQL's syntax, I
included translation tips on how to use the psql equiv of the SHOW
commands. Going from SHOW foo to \d foo is easy, going from \d foo
to SHOW foo is hard and drives me nuts. This'll help userbase
retention of newbies/converts. :)
5) The MySQL mode is just a bounce layer that provides different
syntax wrapping exec_command() so it should provide little in the
way of maintenance headaches. Some of the SHOW commands, however,
don't have \ couterparts, but once they do and that code is
centralized, this feature should come for zero cost.
6) As an administrator, I'd be interested in having an environment
variable that I could set that'd turn on MySQL mode for some of my
bozo users that way they don't complain if they forget the -m
switch. Thoughts?
I'll try and iron out the last of those two bugs/features, but at this
point, would like to see this patch get wider testing/feedback.
Comments, as always, are welcome.
PostgreSQL_usability++
-sc
--
Sean Chittenden
Attachment
Sean Chittenden <sean@chittenden.org> writes:
> 4) guc.c is now in sync with the list of available variables used for
> tab completion
AFAIK, the GUC variables not listed in tab-complete.c were omitted
deliberately. We could have a discussion about the sensefulness of
those decisions, but please do not consider it a bug to be fixed
out-of-hand.
> 6) As an administrator, I'd be interested in having an environment
> variable that I could set that'd turn on MySQL mode for some of my
> bozo users that way they don't complain if they forget the -m
> switch. Thoughts?
Can't you set it in ~/.psqlrc ?
regards, tom lane
> > 4) guc.c is now in sync with the list of available variables used for > > tab completion > > AFAIK, the GUC variables not listed in tab-complete.c were omitted > deliberately. We could have a discussion about the sensefulness of > those decisions, but please do not consider it a bug to be fixed > out-of-hand. Alright, there weren't many omitted GUC's, but those that were omitted did have counterparts that were include already so I figured there was some bit rot going on. > > 6) As an administrator, I'd be interested in having an environment > > variable that I could set that'd turn on MySQL mode for some of my > > bozo users that way they don't complain if they forget the -m > > switch. Thoughts? > > Can't you set it in ~/.psqlrc ? Hrm... ah, ok, done. Patch updated. http://people.freebsd.org/~seanc/patches/patch_postgresql-HEAD::src::bin::psql -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> 4) guc.c is now in sync with the list of available variables used for
> tab completion
>>
>> AFAIK, the GUC variables not listed in tab-complete.c were omitted
>> deliberately. We could have a discussion about the sensefulness of
>> those decisions, but please do not consider it a bug to be fixed
>> out-of-hand.
> Alright, there weren't many omitted GUC's, but those that were
> omitted did have counterparts that were include already so I figured
> there was some bit rot going on.
There could be some of that too. I was just saying that it's not a
foregone conclusion to me that every parameter known to guc.c should
be in the tab completion list.
regards, tom lane
Sean Chittenden writes: > I don't think these should be hacked into the backend/libpq, but I > think it'd be a huge win to hack in "show *" support into psql for > MySQL users so they can type: > > SHOW (databases|tables|views|functions|triggers|schemas); Well, we (will) have the information schema, and if you like you can put it in the path and write select * from tables; etc., which seems just as good. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Sean Chittenden writes:
>> I don't think these should be hacked into the backend/libpq, but I
>> think it'd be a huge win to hack in "show *" support into psql for
>> MySQL users so they can type:
>>
>> SHOW (databases|tables|views|functions|triggers|schemas);
> Well, we (will) have the information schema, and if you like you can put
> it in the path and write
> select * from tables;
> etc., which seems just as good.
I think Sean's idea is not to make it "just as easy as MySQL", it's to
make it "the *same* as MySQL", for the benefit of those that refuse to
learn differently. Them as won't adjust to "\dt" in place of "show
tables" aren't likely to adjust to "select * from tables" either.
Not even (maybe especially not) if it's arguably a standard.
I think the idea has some merit; although I wonder whether it wouldn't
be smarter to put the code in the backend so that you don't need a
parser in psql. The SHOW code could fall back to looking at these
possibilities after it fails to find a match to a GUC variable name.
regards, tom lane
> >> I don't think these should be hacked into the backend/libpq, but > >> I think it'd be a huge win to hack in "show *" support into psql > >> for MySQL users so they can type: > >> > >> SHOW (databases|tables|views|functions|triggers|schemas); > > > Well, we (will) have the information schema, and if you like you > > can put it in the path and write select * from tables; etc., which > > seems just as good. I thought about changing the SHOW commands to executing a SELECT from the information_schema schema, actually, but unless the various \d commands are going to get switched to SELECT'ing from the information_schema, it decreases the likelihood that someone will successfully switch over to using the \d commands in psql. I believe that the \d commands are a good thing and it's good that the backend doesn't have support for the \d commands. Having \d or SHOW commands in the backend would dirty up the sources of the backend, IMHO. > I think Sean's idea is not to make it "just as easy as MySQL", it's > to make it "the *same* as MySQL", for the benefit of those that > refuse to learn differently. Them as won't adjust to "\dt" in place > of "show tables" aren't likely to adjust to "select * from tables" > either. Not even (maybe especially not) if it's arguably a > standard. It's amazing what you can accomplish by taking out your DBAs and racking up a $20 bar tab (note to would be attempters of this method: when the bar tab gets to $50, you find out things you didn't really want to know or weren't intending to hear). So yeah, as Tom said, they know \d [table_name], I've been able to get that much through, but syntactically, it doesn't offer the same syntactic goo that their fingers are used to typing and they _hate_ that there's no SHOW command for tables. It's a usability irk that kills them every time, they type "SHOW [tab]" him. There are a few other things I picked up that evening too. *) MySQL's CLI tab completion is terrible and the SHOW commands work even worse than other tab operations in mysql, this _is_ something that they do like about psql. psql's tab completion is really snappy by comparison. *) The _only_ time that they use the SHOW commands is when they're using a CLI. So as opposed to supporting MySQL's brokenness, I hacked a small parser into psql and added a TIP to the top of the result set printed to stderr that tells the user how to use the equiv \d command. > I think the idea has some merit; although I wonder whether it > wouldn't be smarter to put the code in the backend so that you don't > need a parser in psql. The SHOW code could fall back to looking at > these possibilities after it fails to find a match to a GUC variable > name. Well, I think that the backend should be kept clean of MySQL's nastiness. It's ugly to have a small parser in psql, but I think it's better off than letting MySQL dictate non-existent standards to the rest of the DB community because its developers have struck a chord with the newbie database masses. PostgreSQL is a better database, we shouldn't have to cater to their hackery. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
>> I think the idea has some merit; although I wonder whether it
>> wouldn't be smarter to put the code in the backend so that you don't
>> need a parser in psql. The SHOW code could fall back to looking at
>> these possibilities after it fails to find a match to a GUC variable
>> name.
> Well, I think that the backend should be kept clean of MySQL's
> nastiness.
Keep in mind though that there was already talk of migrating most of the
\d functionality to the backend (primarily as a way of decoupling psql
from catalog version changes). If we were to do that, it would make
good sense to make it accessible via SHOW as well. IMHO anyway.
regards, tom lane
> >> I think the idea has some merit; although I wonder whether it > >> wouldn't be smarter to put the code in the backend so that you > >> don't need a parser in psql. The SHOW code could fall back to > >> looking at these possibilities after it fails to find a match to > >> a GUC variable name. > > > Well, I think that the backend should be kept clean of MySQL's > > nastiness. > > Keep in mind though that there was already talk of migrating most of > the \d functionality to the backend (primarily as a way of > decoupling psql from catalog version changes). If we were to do > that, it would make good sense to make it accessible via SHOW as > well. IMHO anyway. :-/ Yeah, I've been following that from a distance and I'm not so wild about that. I really like that the information_schema has been integrated into the base, but translating the SHOW commands into SELECTs from information_schema on the backend seems like a bad idea unless its going to be done abstract enough via some kind of rewrite engine that allows users to program the database to translate their verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be kinda fun. Getting back to SHOW, what do you want to show or not show? Does the backend show what's most user friendly? If that's the case, do you only show tables that a user has SELECT access to? Does SHOW return tuples like a SELECT? What if a SHOW statement doesn't show what the user is interested in (view definitions)? How about when those view definitions get really long and hard to visually see on a terminal screen? There's no select list available in the SHOW syntax to limit out excessive bits. While adding the ability to set MYSQL_MODE as something that a user could set in their .psqlrc, I thought it'd be the ideal progression to do a few things: 1) change the \d commands to the appropriate SELECT from the information_schema. Doing this'll go a long way toward keeping the structure of the database contained in the database and psql independent. 2) Set a few tunables that specify the select list for the SELECTs from the information_schema that way a user can specify what they see/don't see. 3) SHOW is syntactic user goo that makes MySQL users feel happy and should be in the user interface. Because SHOW is a user interface nicety, real admins that over see database users could change users' .psqlrc files to specify the select list that the user/site wants, which could possibly be even the entire query. Hrm, how's this for a more concise argument: Pushing SHOW/\d into the backend is a bad idea. The backend is a relational database, not a user interface. The information_schema.* tables/views are the SQL sanctioned interface that the backend provides. How a user interfaces with the database/information_schema is something that should be left up to the user interface program (psql) and not pushed into the backend. If a user wants to type "SHOW TABLES LIKE p" instead of "\dt p*", so be it, but that's a user interface concern, not an SQL concern. The SQL way of getting the same data as "SHOW TABLES" is via SELECTing from the information_schema schema. Implementing SQL commands in the backend to make up for MySQL's inability to be forward thinking and consequently hack in a syntax to wrap around their system catalogs for newbie DB users is bad juju. By the same token, doesn't mean PostgreSQL can't provide the same lovey dovey interface that new users expect, it should, however mean that the backend should be left alone to do what it specializes in (being an SQL conformant relational DB) and that the user interface (psql in this case) should be left alone to implement what SHOW TABLES really means. Keep in mind, that the only time that the SHOW commands are used, from what I've been able to ascertain, is when DBAs are in psql and doing basic admin work and exploring/creating their corner of the universe. Anyone who's seriously trying to write a tool to inspect the database knows PostgreSQL reasonably well and uses SELECT + the system catalogs. The target audience for a SHOW syntax isn't the power DBAs or people writing interfaces to examine PostgreSQL, it's the newbie creating a table for a hack project via the CLI (psql). Allowing users to customize the meaning of the \d/SHOW commands would make psql much more powerful than it currently is and would address many of these usability concerns. I'm now thinking that psql should intercept all non-standard SQL calls (bits not starting with SELECT, UPDATE, INSERT, ALTER, etc) and translate them into the appropriate SQL. Having a generic mechanism for doing this would make psql significantly cleaner. Anyway, I'll rest on this topic until I hear whether or not folks would rather have this done in psql or on the backend, but I'd like to get this in place somewhere so that I can stop reworking bits from MySQL to PostgreSQL. If it's determined that the bits should be done in psql, I'll gladly finish things up, clean things up, add the docs, move things over to use the information_schema, and if folks would like, add the appropriate functionality that'll allow folks to configure the \d commands/SHOW via their .psqlrc. -sc -- Sean Chittenden
It's out of date and I did not receive any feedback on it (so I assume it's not very useful), but the patch submitted in late February would easily allow the described 'translation' to occur. Summary: Schema in backend translates a \<command> <arg> command into an sql query which is then to be executed. Logic is still in psql, but the available commands and how data is retrieved from the schema was backend specific. I'm using it here due to it's ability to add new psql commands (since it's just data in a table.) http://archives.postgresql.org/pgsql-patches/2003-02/msg00216.php Anyway, might be useful for thoughts. Simply moving the commands into the backend still leaves us with unsupported new commands in old clients or new commands in old databases (leaving errors) unless the SHOW ... syntax itself is handled by the backend. > :-/ Yeah, I've been following that from a distance and I'm not so wild > about that. I really like that the information_schema has been > integrated into the base, but translating the SHOW commands into > SELECTs from information_schema on the backend seems like a bad idea > unless its going to be done abstract enough via some kind of rewrite > engine that allows users to program the database to translate their > verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be > kinda fun. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Sean Chittenden <sean@chittenden.org> writes:
>> Keep in mind though that there was already talk of migrating most of
>> the \d functionality to the backend (primarily as a way of
>> decoupling psql from catalog version changes). If we were to do
>> that, it would make good sense to make it accessible via SHOW as
>> well. IMHO anyway.
> :-/ Yeah, I've been following that from a distance and I'm not so wild
> about that. I really like that the information_schema has been
> integrated into the base, but translating the SHOW commands into
> SELECTs from information_schema on the backend seems like a bad idea
> unless its going to be done abstract enough via some kind of rewrite
> engine that allows users to program the database to translate their
> verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be
> kinda fun.
Well, I don't want to convert \d into selects from information_schema,
primarily because that would constrain us to showing only things that
are known to the SQL spec --- goodbye, Postgres-specific features
(such as user-definable operators).
I was, however, wondering whether the backend internal support for
"SHOW tables" couldn't be simply to translate it to "SELECT * FROM
some_view". Then it'd be possible for people to customize the output by
replacing the view definition.
> Getting back to SHOW, what do you want to show or not show? Does the
> backend show what's most user friendly? If that's the case, do you
> only show tables that a user has SELECT access to? Does SHOW return
> tuples like a SELECT? What if a SHOW statement doesn't show what the
> user is interested in (view definitions)?
I thought you only wanted MySQL-equivalent functionality here ;-).
Don't tell me they have customizable SHOW output ...
> The information_schema.* tables/views are the SQL sanctioned interface
> that the backend provides.
This argument sounds great in the abstract, but it falls down as soon as
you consider the reality that we want to support things that aren't
SQL-sanctioned. Now, we could define some views that are *not* exactly
INFORMATION_SCHEMA, but at that point the claim that it's a stable
standard interface is looking a lot weaker :-(
regards, tom lane
> >> Keep in mind though that there was already talk of migrating most > >> of the \d functionality to the backend (primarily as a way of > >> decoupling psql from catalog version changes). If we were to do > >> that, it would make good sense to make it accessible via SHOW as > >> well. IMHO anyway. > > > :-/ Yeah, I've been following that from a distance and I'm not so > > wild about that. I really like that the information_schema has > > been integrated into the base, but translating the SHOW commands > > into SELECTs from information_schema on the backend seems like a > > bad idea unless its going to be done abstract enough via some kind > > of rewrite engine that allows users to program the database to > > translate their verbiage into SQL (ex: KILL -> DROP, GET -> > > SELECT), which could be kinda fun. > > Well, I don't want to convert \d into selects from > information_schema, primarily because that would constrain us to > showing only things that are known to the SQL spec --- goodbye, > Postgres-specific features (such as user-definable operators). ::nods:: Good point. All the more the reason to put this in the client. :) > I was, however, wondering whether the backend internal support for > "SHOW tables" couldn't be simply to translate it to "SELECT * FROM > some_view". Then it'd be possible for people to customize the > output by replacing the view definition. Well, my attitude is to arm psql with a good set of defaults (already has some good ones, IMHO), and having it catch \[token1] [token2] and SHOW [token1] and translate it into the appropriate query. If it works out in psql, then leave it. If people complain about it not being available in the backend, then we can move it there in 8.0. :) > > Getting back to SHOW, what do you want to show or not show? Does > > the backend show what's most user friendly? If that's the case, > > do you only show tables that a user has SELECT access to? Does > > SHOW return tuples like a SELECT? What if a SHOW statement > > doesn't show what the user is interested in (view definitions)? > > I thought you only wanted MySQL-equivalent functionality here ;-). > Don't tell me they have customizable SHOW output ... Heh, they don't, but letting psql customize what SHOW means would be a feature that mysql doesn't have and one that'd be reasonably useful, IMHO. > > The information_schema.* tables/views are the SQL sanctioned > > interface that the backend provides. > > This argument sounds great in the abstract, but it falls down as > soon as you consider the reality that we want to support things that > aren't SQL-sanctioned. Now, we could define some views that are > *not* exactly INFORMATION_SCHEMA, but at that point the claim that > it's a stable standard interface is looking a lot weaker :-( Does the spec preclude us from adding views/tables to the information_schema that allow information_schema to be a completely reflective interface into the structure of the backend? I'm worried that things are out of control because the existing, already in use backend's system catalogs aren't user friendly (ex: usename -> username). -sc -- Sean Chittenden
Tom Lane writes: > I think Sean's idea is not to make it "just as easy as MySQL", it's to > make it "the *same* as MySQL", for the benefit of those that refuse to > learn differently. Them as won't adjust to "\dt" in place of "show > tables" aren't likely to adjust to "select * from tables" either. > Not even (maybe especially not) if it's arguably a standard. "Same as MySQL" is impossible. We can pick here and there and add tons of duplicate interfaces, play catch-up when they change them, but there's always going to be a next feature that "would be *really* nice if PostgreSQL could support it and it would surely draw *tons* of users to PostgreSQL". Freely written MySQL code is basically completely incompatible with PostgreSQL, so someone who needs to switch will have to relearn anyway. -- Peter Eisentraut peter_e@gmx.net
> It's out of date and I did not receive any feedback on it (so I assume > it's not very useful), but the patch submitted in late February would > easily allow the described 'translation' to occur. > > Summary: > > Schema in backend translates a \<command> <arg> command into an sql > query which is then to be executed. Logic is still in psql, but the > available commands and how data is retrieved from the schema was backend > specific. > > > I'm using it here due to it's ability to add new psql commands (since > it's just data in a table.) > > http://archives.postgresql.org/pgsql-patches/2003-02/msg00216.php > > > Anyway, might be useful for thoughts. Simply moving the commands into > the backend still leaves us with unsupported new commands in old clients > or new commands in old databases (leaving errors) unless the SHOW ... > syntax itself is handled by the backend. > > > :-/ Yeah, I've been following that from a distance and I'm not so wild > > about that. I really like that the information_schema has been > > integrated into the base, but translating the SHOW commands into > > SELECTs from information_schema on the backend seems like a bad idea > > unless its going to be done abstract enough via some kind of rewrite > > engine that allows users to program the database to translate their > > verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be > > kinda fun. Anyone other than Rod (and now myself) had a chance to look this over? This doesn't really address the lack of a SHOW syntax for new MySQL users, but it sure does open up the possibilities for making it easier to probe the backend. On a related note, any thoughts on the SHOW stuff given that the topic has come back to life on -hackers? -sc -- Sean Chittenden
Attachment
Where do we want to go with this? It is interesting that it maps MySQL SHOW commands on top of our existing SHOW syntax in psql. The patch doesn't look too big. Should this be applied? Sean, I know you have a newer patch the the URL you posted isn't good anymore. This also contains GUC change. Sean, would you show each one you added and we can discuss if any are inappropriate. --------------------------------------------------------------------------- Sean Chittenden wrote: > > > That's a pretty reasonable thought. I work for a shop that sells > > > Postgres support, and even we install MySQL for the Q&D ticket > > > tracking system we recommend because we can't justify the cost to > > > port it to postgres. If the postgres support were there, we would > > > surely be using it. > > > > > How to fix such a situation, I'm not sure. "MySQL Compatability > > > Mode," anyone? :-) > > > > What issues are creating a compatibility problem for you? > > I don't think these should be hacked into the backend/libpq, but I > think it'd be a huge win to hack in "show *" support into psql for > MySQL users so they can type: > > SHOW (databases|tables|views|functions|triggers|schemas); > > I have yet to meet a MySQL user who understands the concept of system > catalogs even though it's just the 'mysql' database (this irritates me > enough as is)... gah, f- it: mysql users be damned, I have three > developers that think that postgresql is too hard to use because they > can't remember "\d [table name]" and I'm tired of hearing them bitch > when I push using PostgreSQL instead of MySQL. I have better things > to do with my time than convert their output to PostgreSQL. Here goes > nothing... > > I've tainted psql and added a MySQL command compatibility layer for > the family of SHOW commands (psql [-m | --mysql]). > > > The attached patch does a few things: > > 1) Implements quite a number of SHOW commands (AGGREGATES, CASTS, > CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES, > DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS, > PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS, > TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS) > > SHOW thing > SHOW thing LIKE pattern > SHOW thing FROM pattern > SHOW HELP ON (topic || ALL); > etc. > > Some of these don't have \ command eqiv's. :( I was tempted to add > them, but opted not to for now, but it'd certainly be a nice to > have. > > 2) Implements the necessary tab completion for the SHOW commands for > the tab happy newbies/folks out there. psql is more friendly than > mysql's CLI now in terms of tab completion for the show commands. > > 3) Few trailing whitespace characters were nuked > > 4) guc.c is now in sync with the list of available variables used for > tab completion > > > Few things to note: > > 1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in > this regard and that it should be INDEXES to be plural along with > the rest of the types, but INDEX is preserved for compatibility. > > 2) There are two bugs that I have yet to address > > 1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does > 2) "SHOW [variable_of_choice];" doesn't work, but "SHOW > [variable_of_choice]\n;" does work... not sure where this > problem is coming from > > 3) I think psql is more usable as a result of this more verbose > syntax, but it's not the prettiest thing on the planet (wrote a > small parser outside of the backend or libraries: I don't want to > get those dirty with MySQL's filth). > > 4) In an attempt to wean people over to PostgreSQL's syntax, I > included translation tips on how to use the psql equiv of the SHOW > commands. Going from SHOW foo to \d foo is easy, going from \d foo > to SHOW foo is hard and drives me nuts. This'll help userbase > retention of newbies/converts. :) > > 5) The MySQL mode is just a bounce layer that provides different > syntax wrapping exec_command() so it should provide little in the > way of maintenance headaches. Some of the SHOW commands, however, > don't have \ couterparts, but once they do and that code is > centralized, this feature should come for zero cost. > > 6) As an administrator, I'd be interested in having an environment > variable that I could set that'd turn on MySQL mode for some of my > bozo users that way they don't complain if they forget the -m > switch. Thoughts? > > > I'll try and iron out the last of those two bugs/features, but at this > point, would like to see this patch get wider testing/feedback. > Comments, as always, are welcome. > > PostgreSQL_usability++ > > -sc > > -- > Sean Chittenden [ Attachment, skipping... ] > > ---------------------------(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 -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian writes: > Where do we want to go with this? It is interesting that it maps MySQL > SHOW commands on top of our existing SHOW syntax in psql. The patch > doesn't look too big. The response to "Are we losing momentum?" isn't to add redundant syntax for nonstandard features that we have no control over. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> Where do we want to go with this? It is interesting that it maps MySQL
>> SHOW commands on top of our existing SHOW syntax in psql. The patch
>> doesn't look too big.
> The response to "Are we losing momentum?" isn't to add redundant syntax
> for nonstandard features that we have no control over.
I'm of two minds about it myself. I don't like trying to play follow-the-
leader with a moving target. But if you think of it as trying to win
over converts from MySQL, it seems a lot more palatable.
It would also be interesting to combine this with Rod's idea of driving
describe-type queries by table instead of hardwired code. Imagine that
the backend's "show foo" command first looks for "foo" as a GUC
variable, as it does now, but upon failing to find one it looks in a
system table for a query associated with the name "foo". If it finds
such a query, it runs it and sends back the result. Now, not only can
we emulate "show tables", but people can easily add application-specific
"show whatever" commands, which seems tremendously cool.
There are some safety and protection issues to be solved here (probably
only superusers should be allowed to modify the query table, and we
should restrict the form of the query to be a single SELECT command)
but I can't think of any showstoppers. Now that we've abandoned backend
autocommit there are no technical reasons that SHOW shouldn't be allowed
to run a SELECT query.
I did not care for the original patch (which IIRC made psql recognize
"show" commands, rather than doing it in the backend) but something like
the above seems reasonably clean.
regards, tom lane
> >> Where do we want to go with this? It is interesting that it maps > >> MySQL SHOW commands on top of our existing SHOW syntax in psql. > >> The patch doesn't look too big. > > > > The response to "Are we losing momentum?" isn't to add redundant > > syntax for nonstandard features that we have no control over. > > I'm of two minds about it myself. I don't like trying to play > follow-the- leader with a moving target. But if you think of it as > trying to win over converts from MySQL, it seems a lot more > palatable. The _only_ reason I added this was to aid in the conversion of MySQL users who don't know how to navigate their way through psql and PostgreSQL. If you read through the patch, the extended SHOW commands are only enabled if you turn them on via the --mysql option or by setting an option (don't recall at the moment) that way you can turn these on by default in a person's .psqlrc file. With Win32 coming down the pipe (right??? *cough cough* :-P), converting existing users is going to be important for PostgreSQL's long term success if it seeks to gain market share and momentum. > It would also be interesting to combine this with Rod's idea of > driving describe-type queries by table instead of hardwired code. > Imagine that the backend's "show foo" command first looks for "foo" > as a GUC variable, as it does now, but upon failing to find one it > looks in a system table for a query associated with the name "foo". > If it finds such a query, it runs it and sends back the result. > Now, not only can we emulate "show tables", but people can easily > add application-specific "show whatever" commands, which seems > tremendously cool. I really like the ability to program in queries or syntaxes into the backend, but as it stands, SHOW foo would have to be pretty smart to handle the LIKE clauses and other bits. And how would tab completion be handled in psql? I can't remember if I implemented stuff like, SHOW TABLES IN SCHEMA foo, but 'SHOW TABLES LIKE a%' was quite a hit to the people I have using the patch and I don't see how that'd be applicable with what has been proposed so far. The extra verbosity and tab completion-ability is important for the newbie, it's an interactive environment that lets them learn and explore in a proactive manner. Having IN SCHEMA vs LIKE handled on the backend would be a rather complex generic model, but in an ideal world, one that I'd prefer if done right. > There are some safety and protection issues to be solved here > (probably only superusers should be allowed to modify the query > table, and we should restrict the form of the query to be a single > SELECT command) but I can't think of any showstoppers. Now that > we've abandoned backend autocommit there are no technical reasons > that SHOW shouldn't be allowed to run a SELECT query. > > I did not care for the original patch (which IIRC made psql > recognize "show" commands, rather than doing it in the backend) but > something like the above seems reasonably clean. Well, unless something is done very cleanly and abstractly, and can be extended to handle IN SCHEMA, LIKE, etc., I'm of the opinion that this kinda stuff belongs on the client side of things and _not_ in the backend. Putting MySQL's brokenness in the backend seems, well, to perpetuate the brokenness and acknowledge that their brokenness isn't really that broken: not a message I'm fond of. Putting MySQL's brokenness in psql and hiding it behind the --mysql CLI option, however, is tolerably broken, IMHO and of the same use as ora2pg or mysql2pg. Bruce wrote: > Where do we want to go with this? It is interesting that it maps > MySQL SHOW commands on top of our existing SHOW syntax in psql. The > patch doesn't look too big. > > Should this be applied? Sean, I know you have a newer patch the the > URL you posted isn't good anymore. This also contains GUC change. > Sean, would you show each one you added and we can discuss if any > are inappropriate. The GUC change was just flushing out various GUC options that weren't available as tab completion options... the usefulness of those GUCs could be debated, but at least there's a complete list in the CLI now... having this pulled from the server would be wise, IMHO, but I didn't spend the time to add that at the time. If there's interest, I can do that. I have the patch someplace, don't worry about that. The patch isn't 100% correct as it stands: there is a problem in recognizing the end of a SHOW command so that the query buffer was considered non-empty (wasn't scanning properly for ;'s to emulate the end of a query: something that's not necessary with the normal \ syntax). Other than that, there are no bugs that I'm aware of and the work around is to issue a \r: not an earth shattering problem given psql has a history. Before I finished, I wanted to get some review/thoughts on the patch before I completed the work and sent it off only to have it rejected. I know Tom doesn't like the idea because it's not "clean," but I can't help but feel that adding similar functionality to the backend is some how contaminating PostgreSQL and acknowledges MySQL's done something creditable, which is too big of an admission, especially since their product is pretty fundamentally flawed in terms of spec compliance. The last thing we want is to have their brokenness turn into spec and for that to become the norm. So, as life would have it, I'm an advocate of adding the SHOW syntax to psql and an opponent of having it in the backend, the core developers would rather have the SHOW syntax in the backend because it's not clean to have it in the front end. *shrug* psql has to have a quasi-parser in the front end as is to handle tab completion, so I don't really buy the argument that it's bad to have a parser in the front end (sorry Tom). In fact, I actually think that the parser in psql needs to be _beefed up_ and made _smarter_ that way psql's more user friendly, even if that means increasing the complexity of psql. At the moment, there are short falls with psql's ability to perform tab completion of column names on aliased tables, etc. Some of it's non-trivial and won't ever see the light of day in psql, but there is room for improvement. Anyway, usability is a problem for clients, not for servers. MySQL usability belongs in the client, not the backend.... and hidden behind a CLI flag at that 'cause I don't available in my personal development environment. And before someone says, "that's fine and dandy, who's going to do the work on psql," I'll gladly work on psql and even go so far as to clean up the code now that someone's around to apply the patches. :) *listens and waits before updating the patch and fixing the above bugs* -sc -- Sean Chittenden
> It would also be interesting to combine this with Rod's idea of driving > describe-type queries by table instead of hardwired code. Imagine that > the backend's "show foo" command first looks for "foo" as a GUC > variable, as it does now, but upon failing to find one it looks in a > system table for a query associated with the name "foo". If it finds > such a query, it runs it and sends back the result. Now, not only can > we emulate "show tables", but people can easily add application-specific > "show whatever" commands, which seems tremendously cool. Easy enough to accomplish for the most part. I suppose the most difficult part is whether we support arbitrary syntax? SHOW INDEXES ON TABLE <bleah>; SHOW TABLES; SHOW DATABASES; SHOW COLUMNS FROM <table>; <-- Long form of DESCRIBE <table> I believe all of the above is valid MySQL syntax, so we would need to be able to work with a list of colId, rather than a single one. This does not help with tab completion or help for the above items. Though one could certainly argue help on available commands should come from the backend, and psql could be taught how to read the table to determine how to deal with tab completion. One significant downside is that describe commands would require an initdb when updated. Oh, if it's the backend doing the work, the queries should probably be functions, not free-form queries. Simply makes it easier to inject variables into the right place. In the initial patch, psql is using a prepared statement for this work with the side benefit that the plan is cached. An SQL function would accomplish the same thing. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Tom Lane writes: > Now, not only can we emulate "show tables", but people can easily add > application-specific "show whatever" commands, which seems tremendously > cool. We already have that. They're called views. -- Peter Eisentraut peter_e@gmx.net
> > Now, not only can we emulate "show tables", but people can easily > > add application-specific "show whatever" commands, which seems > > tremendously cool. > > We already have that. They're called views. Um, I'm interested in aiding in the conversion of users from MySQL to PostgreSQL (how ever it happens, I don't really care). Tom and Rod are interested in an extensible/programmable SHOW syntax (very cool and much more interesting than the hack I put together). Views solve none of the above problems. -sc -- Sean Chittenden
Sean Chittenden writes: > Um, I'm interested in aiding in the conversion of users from MySQL to > PostgreSQL (how ever it happens, I don't really care). Your approach to that reminds me of those alias dir='ls' alias md='mkdir' things that Linux distributors once stuck (or still stick?) in the default profile files, presumably to help conversion from DOS. It's pretty pointless, because Linux is still very different from DOS, and once you want to do something besides showing or changing directories, you will need documentation and training. That is what the MySQL conversion process needs as well, otherwise you're not converting, you're emulating, and that is not a game you can win. -- Peter Eisentraut peter_e@gmx.net
> > Um, I'm interested in aiding in the conversion of users from MySQL
> > to PostgreSQL (how ever it happens, I don't really care).
>
> Your approach to that reminds me of those
>
> alias dir='ls'
> alias md='mkdir'
>
> things that Linux distributors once stuck (or still stick?) in the
> default profile files, presumably to help conversion from DOS. It's
> pretty pointless, because Linux is still very different from DOS,
> and once you want to do something besides showing or changing
> directories, you will need documentation and training.
Well, interestingly enough, those commands work for getting people in
the door and to the point that they're able to learn more. The first
step to any kind of adult education or reeducation is to have concepts
that the people are familiar with (in this case MySQL) be translated
into the concepts of the area that they're trying to learn. If you'd
have read the original patch that I'd posted, you'd see that I'd done
that by adding a TIP section to the top of the response.
*SNIP*
SHOW COLUMNS FROM [tblname];
TIP: In psql, "SHOW COLUMNS FROM [tblname]" is natively written as \dt [tblname]
[normal output from \dt tblname]
*END SNIP*
The point of my patch was to aid the conversion, not to gimp along
b0rk3d habits from MySQL.
> That is what the MySQL conversion process needs as well, otherwise
> you're not converting, you're emulating, and that is not a game you
> can win.
Emulation within reason. dir->ls and md->mkdir worked for a handful
of people that I've transitioned into the UNIX world from Win32 land,
in fact, I have one friend from school who's been so successful that
he's converted from using Win32 on his desktop to using Linux, worked
with me on a job where we were hacking mod_perl on a site pushing in
excess of 80Mbps to 25M people a day, but still types dir to this day.
Not bad for an aero student who graduated with a 4.0 in his major,
exceedingly bright, adaptive, learns fast, etc. My point is that
regardless of how bright the person or what the right invocation, aids
like these help get people in the door and if they like what they see
once they're through the door, they'll stay. Once people try and use
PostgreSQL, they stay. When people try MySQL, they're left wanting or
needing more and are bound by the limits of the software... that's not
really the case with PostgreSQL.
To get people to try, you play the association or emulation game and
it works. Ask anyone in adult education and they will say the same.
The Internet was an "information super highway" because that was
something that people could grasp, regardless of how flawed it really
is. Bandwidth is thought of as pipes in various sizes diameters, a
much better analogy. To geeks, broadcast is explained as the same as
radio and unicast as satellite TV. To communication majors, TCP is
descried as a letter that's been chopped up into a thousand numbered
pieces and sent to the other side of the US via the postal mail.
Leche is milk to English speakers learning Spanish. One way or
another, you have to play the game of working within the understanding
of the target audience, in this case, MySQL users who use a SHOW
TABLES type syntax.
-sc
--
Sean Chittenden
Wow, this is a good argument! I must admit I made 'ps -ef' work on BSD because I was just so used to it on mainframe Unix. There is that 'fingers type without thinking' thing, and I think that is what he is talking about. I wonder if we should just support SHOW TABLES or the most common ones. Maybe emulation is the wrong approach --- maybe we just need 'finger thinking' shortcuts. --------------------------------------------------------------------------- Sean Chittenden wrote: > > > Um, I'm interested in aiding in the conversion of users from MySQL > > > to PostgreSQL (how ever it happens, I don't really care). > > > > Your approach to that reminds me of those > > > > alias dir='ls' > > alias md='mkdir' > > > > things that Linux distributors once stuck (or still stick?) in the > > default profile files, presumably to help conversion from DOS. It's > > pretty pointless, because Linux is still very different from DOS, > > and once you want to do something besides showing or changing > > directories, you will need documentation and training. > > Well, interestingly enough, those commands work for getting people in > the door and to the point that they're able to learn more. The first > step to any kind of adult education or reeducation is to have concepts > that the people are familiar with (in this case MySQL) be translated > into the concepts of the area that they're trying to learn. If you'd > have read the original patch that I'd posted, you'd see that I'd done > that by adding a TIP section to the top of the response. > > *SNIP* > SHOW COLUMNS FROM [tblname]; > > TIP: In psql, "SHOW COLUMNS FROM [tblname]" is natively written as \dt [tblname] > > [normal output from \dt tblname] > *END SNIP* > > The point of my patch was to aid the conversion, not to gimp along > b0rk3d habits from MySQL. > > > That is what the MySQL conversion process needs as well, otherwise > > you're not converting, you're emulating, and that is not a game you > > can win. > > Emulation within reason. dir->ls and md->mkdir worked for a handful > of people that I've transitioned into the UNIX world from Win32 land, > in fact, I have one friend from school who's been so successful that > he's converted from using Win32 on his desktop to using Linux, worked > with me on a job where we were hacking mod_perl on a site pushing in > excess of 80Mbps to 25M people a day, but still types dir to this day. > Not bad for an aero student who graduated with a 4.0 in his major, > exceedingly bright, adaptive, learns fast, etc. My point is that > regardless of how bright the person or what the right invocation, aids > like these help get people in the door and if they like what they see > once they're through the door, they'll stay. Once people try and use > PostgreSQL, they stay. When people try MySQL, they're left wanting or > needing more and are bound by the limits of the software... that's not > really the case with PostgreSQL. > > To get people to try, you play the association or emulation game and > it works. Ask anyone in adult education and they will say the same. > The Internet was an "information super highway" because that was > something that people could grasp, regardless of how flawed it really > is. Bandwidth is thought of as pipes in various sizes diameters, a > much better analogy. To geeks, broadcast is explained as the same as > radio and unicast as satellite TV. To communication majors, TCP is > descried as a letter that's been chopped up into a thousand numbered > pieces and sent to the other side of the US via the postal mail. > Leche is milk to English speakers learning Spanish. One way or > another, you have to play the game of working within the understanding > of the target audience, in this case, MySQL users who use a SHOW > TABLES type syntax. > > -sc > > -- > Sean Chittenden > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I assume we agreed against adding a MySQL mode --- just verifying. --------------------------------------------------------------------------- Sean Chittenden wrote: > > > That's a pretty reasonable thought. I work for a shop that sells > > > Postgres support, and even we install MySQL for the Q&D ticket > > > tracking system we recommend because we can't justify the cost to > > > port it to postgres. If the postgres support were there, we would > > > surely be using it. > > > > > How to fix such a situation, I'm not sure. "MySQL Compatability > > > Mode," anyone? :-) > > > > What issues are creating a compatibility problem for you? > > I don't think these should be hacked into the backend/libpq, but I > think it'd be a huge win to hack in "show *" support into psql for > MySQL users so they can type: > > SHOW (databases|tables|views|functions|triggers|schemas); > > I have yet to meet a MySQL user who understands the concept of system > catalogs even though it's just the 'mysql' database (this irritates me > enough as is)... gah, f- it: mysql users be damned, I have three > developers that think that postgresql is too hard to use because they > can't remember "\d [table name]" and I'm tired of hearing them bitch > when I push using PostgreSQL instead of MySQL. I have better things > to do with my time than convert their output to PostgreSQL. Here goes > nothing... > > I've tainted psql and added a MySQL command compatibility layer for > the family of SHOW commands (psql [-m | --mysql]). > > > The attached patch does a few things: > > 1) Implements quite a number of SHOW commands (AGGREGATES, CASTS, > CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES, > DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS, > PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS, > TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS) > > SHOW thing > SHOW thing LIKE pattern > SHOW thing FROM pattern > SHOW HELP ON (topic || ALL); > etc. > > Some of these don't have \ command eqiv's. :( I was tempted to add > them, but opted not to for now, but it'd certainly be a nice to > have. > > 2) Implements the necessary tab completion for the SHOW commands for > the tab happy newbies/folks out there. psql is more friendly than > mysql's CLI now in terms of tab completion for the show commands. > > 3) Few trailing whitespace characters were nuked > > 4) guc.c is now in sync with the list of available variables used for > tab completion > > > Few things to note: > > 1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in > this regard and that it should be INDEXES to be plural along with > the rest of the types, but INDEX is preserved for compatibility. > > 2) There are two bugs that I have yet to address > > 1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does > 2) "SHOW [variable_of_choice];" doesn't work, but "SHOW > [variable_of_choice]\n;" does work... not sure where this > problem is coming from > > 3) I think psql is more usable as a result of this more verbose > syntax, but it's not the prettiest thing on the planet (wrote a > small parser outside of the backend or libraries: I don't want to > get those dirty with MySQL's filth). > > 4) In an attempt to wean people over to PostgreSQL's syntax, I > included translation tips on how to use the psql equiv of the SHOW > commands. Going from SHOW foo to \d foo is easy, going from \d foo > to SHOW foo is hard and drives me nuts. This'll help userbase > retention of newbies/converts. :) > > 5) The MySQL mode is just a bounce layer that provides different > syntax wrapping exec_command() so it should provide little in the > way of maintenance headaches. Some of the SHOW commands, however, > don't have \ couterparts, but once they do and that code is > centralized, this feature should come for zero cost. > > 6) As an administrator, I'd be interested in having an environment > variable that I could set that'd turn on MySQL mode for some of my > bozo users that way they don't complain if they forget the -m > switch. Thoughts? > > > I'll try and iron out the last of those two bugs/features, but at this > point, would like to see this patch get wider testing/feedback. > Comments, as always, are welcome. > > PostgreSQL_usability++ > > -sc > > -- > Sean Chittenden [ Attachment, skipping... ] > > ---------------------------(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 -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > I assume we agreed against adding a MySQL mode --- just verifying. We agreed that applications that need schema information are much better off using the schema views. Jan > > --------------------------------------------------------------------------- > > Sean Chittenden wrote: >> > > That's a pretty reasonable thought. I work for a shop that sells >> > > Postgres support, and even we install MySQL for the Q&D ticket >> > > tracking system we recommend because we can't justify the cost to >> > > port it to postgres. If the postgres support were there, we would >> > > surely be using it. >> > >> > > How to fix such a situation, I'm not sure. "MySQL Compatability >> > > Mode," anyone? :-) >> > >> > What issues are creating a compatibility problem for you? >> >> I don't think these should be hacked into the backend/libpq, but I >> think it'd be a huge win to hack in "show *" support into psql for >> MySQL users so they can type: >> >> SHOW (databases|tables|views|functions|triggers|schemas); >> >> I have yet to meet a MySQL user who understands the concept of system >> catalogs even though it's just the 'mysql' database (this irritates me >> enough as is)... gah, f- it: mysql users be damned, I have three >> developers that think that postgresql is too hard to use because they >> can't remember "\d [table name]" and I'm tired of hearing them bitch >> when I push using PostgreSQL instead of MySQL. I have better things >> to do with my time than convert their output to PostgreSQL. Here goes >> nothing... >> >> I've tainted psql and added a MySQL command compatibility layer for >> the family of SHOW commands (psql [-m | --mysql]). >> >> >> The attached patch does a few things: >> >> 1) Implements quite a number of SHOW commands (AGGREGATES, CASTS, >> CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES, >> DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS, >> PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS, >> TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS) >> >> SHOW thing >> SHOW thing LIKE pattern >> SHOW thing FROM pattern >> SHOW HELP ON (topic || ALL); >> etc. >> >> Some of these don't have \ command eqiv's. :( I was tempted to add >> them, but opted not to for now, but it'd certainly be a nice to >> have. >> >> 2) Implements the necessary tab completion for the SHOW commands for >> the tab happy newbies/folks out there. psql is more friendly than >> mysql's CLI now in terms of tab completion for the show commands. >> >> 3) Few trailing whitespace characters were nuked >> >> 4) guc.c is now in sync with the list of available variables used for >> tab completion >> >> >> Few things to note: >> >> 1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in >> this regard and that it should be INDEXES to be plural along with >> the rest of the types, but INDEX is preserved for compatibility. >> >> 2) There are two bugs that I have yet to address >> >> 1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does >> 2) "SHOW [variable_of_choice];" doesn't work, but "SHOW >> [variable_of_choice]\n;" does work... not sure where this >> problem is coming from >> >> 3) I think psql is more usable as a result of this more verbose >> syntax, but it's not the prettiest thing on the planet (wrote a >> small parser outside of the backend or libraries: I don't want to >> get those dirty with MySQL's filth). >> >> 4) In an attempt to wean people over to PostgreSQL's syntax, I >> included translation tips on how to use the psql equiv of the SHOW >> commands. Going from SHOW foo to \d foo is easy, going from \d foo >> to SHOW foo is hard and drives me nuts. This'll help userbase >> retention of newbies/converts. :) >> >> 5) The MySQL mode is just a bounce layer that provides different >> syntax wrapping exec_command() so it should provide little in the >> way of maintenance headaches. Some of the SHOW commands, however, >> don't have \ couterparts, but once they do and that code is >> centralized, this feature should come for zero cost. >> >> 6) As an administrator, I'd be interested in having an environment >> variable that I could set that'd turn on MySQL mode for some of my >> bozo users that way they don't complain if they forget the -m >> switch. Thoughts? >> >> >> I'll try and iron out the last of those two bugs/features, but at this >> point, would like to see this patch get wider testing/feedback. >> Comments, as always, are welcome. >> >> PostgreSQL_usability++ >> >> -sc >> >> -- >> Sean Chittenden > > [ Attachment, skipping... ] > >> >> ---------------------------(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 > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Personally, I think adding the SHOW commands would be a good thing ... psql is nice with its \df to get information without having to learn all the JOINs required ... having that ability easily from any of the interfaces would definitely be a plus ... to me, its not about MySQL compatibility, but about a small improvement to ease of use :) On Sat, 16 Aug 2003, Bruce Momjian wrote: > > I assume we agreed against adding a MySQL mode --- just verifying. > > --------------------------------------------------------------------------- > > Sean Chittenden wrote: > > > > That's a pretty reasonable thought. I work for a shop that sells > > > > Postgres support, and even we install MySQL for the Q&D ticket > > > > tracking system we recommend because we can't justify the cost to > > > > port it to postgres. If the postgres support were there, we would > > > > surely be using it. > > > > > > > How to fix such a situation, I'm not sure. "MySQL Compatability > > > > Mode," anyone? :-) > > > > > > What issues are creating a compatibility problem for you? > > > > I don't think these should be hacked into the backend/libpq, but I > > think it'd be a huge win to hack in "show *" support into psql for > > MySQL users so they can type: > > > > SHOW (databases|tables|views|functions|triggers|schemas); > > > > I have yet to meet a MySQL user who understands the concept of system > > catalogs even though it's just the 'mysql' database (this irritates me > > enough as is)... gah, f- it: mysql users be damned, I have three > > developers that think that postgresql is too hard to use because they > > can't remember "\d [table name]" and I'm tired of hearing them bitch > > when I push using PostgreSQL instead of MySQL. I have better things > > to do with my time than convert their output to PostgreSQL. Here goes > > nothing... > > > > I've tainted psql and added a MySQL command compatibility layer for > > the family of SHOW commands (psql [-m | --mysql]). > > > > > > The attached patch does a few things: > > > > 1) Implements quite a number of SHOW commands (AGGREGATES, CASTS, > > CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES, > > DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS, > > PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS, > > TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS) > > > > SHOW thing > > SHOW thing LIKE pattern > > SHOW thing FROM pattern > > SHOW HELP ON (topic || ALL); > > etc. > > > > Some of these don't have \ command eqiv's. :( I was tempted to add > > them, but opted not to for now, but it'd certainly be a nice to > > have. > > > > 2) Implements the necessary tab completion for the SHOW commands for > > the tab happy newbies/folks out there. psql is more friendly than > > mysql's CLI now in terms of tab completion for the show commands. > > > > 3) Few trailing whitespace characters were nuked > > > > 4) guc.c is now in sync with the list of available variables used for > > tab completion > > > > > > Few things to note: > > > > 1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in > > this regard and that it should be INDEXES to be plural along with > > the rest of the types, but INDEX is preserved for compatibility. > > > > 2) There are two bugs that I have yet to address > > > > 1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does > > 2) "SHOW [variable_of_choice];" doesn't work, but "SHOW > > [variable_of_choice]\n;" does work... not sure where this > > problem is coming from > > > > 3) I think psql is more usable as a result of this more verbose > > syntax, but it's not the prettiest thing on the planet (wrote a > > small parser outside of the backend or libraries: I don't want to > > get those dirty with MySQL's filth). > > > > 4) In an attempt to wean people over to PostgreSQL's syntax, I > > included translation tips on how to use the psql equiv of the SHOW > > commands. Going from SHOW foo to \d foo is easy, going from \d foo > > to SHOW foo is hard and drives me nuts. This'll help userbase > > retention of newbies/converts. :) > > > > 5) The MySQL mode is just a bounce layer that provides different > > syntax wrapping exec_command() so it should provide little in the > > way of maintenance headaches. Some of the SHOW commands, however, > > don't have \ couterparts, but once they do and that code is > > centralized, this feature should come for zero cost. > > > > 6) As an administrator, I'd be interested in having an environment > > variable that I could set that'd turn on MySQL mode for some of my > > bozo users that way they don't complain if they forget the -m > > switch. Thoughts? > > > > > > I'll try and iron out the last of those two bugs/features, but at this > > point, would like to see this patch get wider testing/feedback. > > Comments, as always, are welcome. > > > > PostgreSQL_usability++ > > > > -sc > > > > -- > > Sean Chittenden > > [ Attachment, skipping... ] > > > > > ---------------------------(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 > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> Bruce Momjian wrote: > >I assume we agreed against adding a MySQL mode --- just verifying. > > We agreed that applications that need schema information are much better > off using the schema views. > > Jan Heh, I don't think there was any agreement on anything in that thread, everyone had their own view (no pun intended). > From: The Hermit Hacker <scrappy@postgresql.org> > > Personally, I think adding the SHOW commands would be a good thing > ... psql is nice with its \df to get information without having to > learn all the JOINs required ... having that ability easily from any > of the interfaces would definitely be a plus ... to me, its not > about MySQL compatibility, but about a small improvement to ease of > use :) Which goes back to the point about there being little agreement on this patch or its issues. A handful of folks think it's a _user interface_ issue (read: psql, phppgadmin, pgadminIII, etc) and would be good for converting MySQL users to PostgreSQL (or simply because its easy and less obtuse than a \ command), others thought it was a fugly hack to have a parser in the front end and that it should be handled on the backend by extending SQL to conform to MySQL's interface (that some argue is incorrect and would unjustly bloat the backend) that way all clients have the SHOW syntax (thus averting a possible FAQ), and others took a more elitist mindset and simply thought that everyone should just select from the information schemas. *shrug* I tabled working on the patch until there was some kind of agreement from someone with commit privs and am waiting to pick up quashing the remaining parser state bug until after 7.4's out the door or there's renewed interest from non-users. -sc -- Sean Chittenden
Short summary:
This patch allows ISO 8601 "time intervals" using the "format
with time-unit designators" to specify postgresql "intervals".
Below I have (A) What these time intervals are, (B) What I
modified to support them, (C) Issues with intervals I want
to bring up, and (D) a patch supporting them.
It's helpful to me. Any feedback is appreciated. If you
did want to consider including it, let me know what to clean
up. If not, I thought I'd just put it here if anyone else finds
it useful too.
Thanks for your time,
Ron Mayer
Longer:
(A) What these intervals are.
ISO 8601, the standard from which PostgreSQL gets some of it's
time syntax, also has a specification for "time-intervals".
In particular, section 5.5.4.2 has a "Representation of
time-interval by duration only" which I believe maps
nicely to ISO intervals.
Compared to the ISO 8601 time interval specification, the
postgresql interval syntax is quite verbose. For example:
Postgresql interval: ISO8601 Interval
---------------------------------------------------
'1 year 6 months' 'P1Y6M'
'3 hours 25 minutes 42 seconds' 'PT3H25M42S'
Yeah, it's uglier, but it sure is short which can make
for quicker typing and shorter scripts, and if for some
strange reason you had an application using this format
it's nice not to have to translate.
The syntax is as follows:
Basic extended format: PnYnMnDTnHnMnS
PnW
Where everything before the "T" is a date-part and everything
after is a time-part. W is for weeks.
In the date-part, Y=Year, M=Month, D=Day
In the time-part, H=Hour, M=Minute, S=Second
Much more info can be found from the draft standard
ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
The final standard's only available for $$$ so I didn't
look at it. Some other sites imply that this part didn't
change from the last draft to the standard.
(B) This change was made by adding two functions to "datetime.c"
next to where DecodeInterval parses the normal interval syntax.
A total of 313 lines were added, including comments and sgml docs.
Of these only 136 are actual code, the rest, comments, whitespace, etc.
One new function "DecodeISO8601Interval" follows the style of
"DecodeInterval" below it, and trys to strictly follow the ISO
syntax. If it doesn't match, it'll return -1 and the old syntax
will be checked as before.
The first test (first character of the first field must be 'P',
and second character must be 'T' or '\0') should be fast so I don't
think this will impact performance of existing code.
The second function ("adjust_fval") is just a small helper-function
to remove some of the cut&paste style that DecodeInterval used.
It seems to work.
=======================================================================
betadb=# select 'P1M15DT12H30M7S'::interval;
interval
------------------------
1 mon 15 days 12:30:07
(1 row)
betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
interval
------------------------
1 mon 15 days 12:30:07
(1 row)
=====================================================================
(C) Open issues with intervals, and questions I'd like to ask.
1. DecodeInterval seems to have a hardcoded '.' for specifying
fractional times. ISO 8601 states that both '.' and ',' are
ok, but "of these, the comma is the preferred sign".
In DecodeISO8601Interval I loosened the test to allow
both but left it as it was in DecodeInterval. Should
both be changed to make them more consistant?
2. In "DecodeInterval", fractional weeks and fractional months
can produce seconds; but fractional years can not (rounded
to months). I didn't understand the reasoning for this, so
I left it the same, and followed the same convention for
ISO intervals. Should I change this?
3. I could save a bunch of copy-paste-lines-of-code from the
pre-existing DecodeInterval by calling the adjust_fval helper
function. The tradeoff is a few extra function-calls when
decoding an interval. However I didn't want to risk changes
to the existing part unless you guys encourage me to do so.
(D) The patch.
Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
retrieving revision 1.123
diff -u -1 -0 -r1.123 datatype.sgml
--- doc/src/sgml/datatype.sgml 31 Aug 2003 17:32:18 -0000 1.123
+++ doc/src/sgml/datatype.sgml 8 Sep 2003 04:04:58 -0000
@@ -1735,20 +1735,71 @@
Quantities of days, hours, minutes, and seconds can be specified without
explicit unit markings. For example, <literal>'1 12:59:10'</> is read
the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
</para>
<para>
The optional precision
<replaceable>p</replaceable> should be between 0 and 6, and
defaults to the precision of the input literal.
</para>
+
+
+ <para>
+ Alternatively, <type>interval</type> values can be written as
+ ISO 8601 time intervals, using the "Format with time-unit designators".
+ This format always starts with the character <literal>'P'</>, followed
+ by a string of values followed by single character time-unit designators.
+ A <literal>'T'</> separates the date and time parts of the interval.
+ </para>
+
+ <para>
+ Format: PnYnMnDTnHnMnS
+ </para>
+ <para>
+ In this format, <literal>'n'</> gets replaced by a number, and
+ <literal>Y</> represents years,
+ <literal>M</> (in the date part) months,
+ <literal>D</> months,
+ <literal>H</> hours,
+ <literal>M</> (in the time part) minutes,
+ and <literal>S</> seconds.
+ </para>
+
+
+ <table id="interval-example-table">
+ <title>Interval Example</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Traditional</entry>
+ <entry>ISO-8601 time-interval</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>1 month</entry>
+ <entry>P1M</entry>
+ </row>
+ <row>
+ <entry>1 hour 30 minutes</entry>
+ <entry>PT1H30M</entry>
+ </row>
+ <row>
+ <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
+ <entry>P2Y10M15DT10H30M20S</entry>
+ </row>
+ </tbody>
+ </thead>
+ </table>
+
+ </para>
</sect3>
<sect3>
<title>Special Values</title>
<indexterm>
<primary>time</primary>
<secondary>constants</secondary>
</indexterm>
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.116
diff -u -1 -0 -r1.116 datetime.c
--- src/backend/utils/adt/datetime.c 27 Aug 2003 23:29:28 -0000 1.116
+++ src/backend/utils/adt/datetime.c 8 Sep 2003 04:04:59 -0000
@@ -30,20 +30,21 @@
struct tm * tm, fsec_t *fsec, int *is2digits);
static int DecodeNumberField(int len, char *str,
int fmask, int *tmask,
struct tm * tm, fsec_t *fsec, int *is2digits);
static int DecodeTime(char *str, int fmask, int *tmask,
struct tm * tm, fsec_t *fsec);
static int DecodeTimezone(char *str, int *tzp);
static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
static void TrimTrailingZeros(char *str);
+static int DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
int day_tab[2][13] = {
{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
{31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
char *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
char *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
@@ -2872,30 +2873,271 @@
default:
*val = tp->value;
break;
}
}
return type;
}
+void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
+{
+ int sec;
+ fval *= scale;
+ sec = fval;
+ tm->tm_sec += sec;
+#ifdef HAVE_INT64_TIMESTAMP
+ *fsec += ((fval - sec) * 1000000);
+#else
+ *fsec += (fval - sec);
+#endif
+}
+
+
+/* DecodeISO8601Interval()
+ *
+ * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
+ * time-interval by duration only."
+ * Basic extended format: PnYnMnDTnHnMnS
+ * PnW
+ * For more info.
+ * http://www.astroclark.freeserve.co.uk/iso8601/index.html
+ * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
+ *
+ * Examples: P1D for 1 day
+ * PT1H for 1 hour
+ * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+ *
+ * The first field is exactly "p" or "pt" it may be of this type.
+ *
+ * Returns -1 if the field is not of this type.
+ *
+ * It pretty strictly checks the spec, with the two exceptions
+ * that a week field ('W') may coexist with other units, and that
+ * this function allows decimals in fields other than the least
+ * significant units.
+ */
+int
+DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
+{
+ char *cp;
+ int fmask = 0,
+ tmask;
+ int val;
+ double fval;
+ int arg;
+ int datepart;
+
+ /*
+ * An ISO 8601 "time-interval by duration only" must start
+ * with a 'P'. If it contains a date-part, 'p' will be the
+ * only character in the field. If it contains no date part
+ * it will contain exactly to characters 'PT' indicating a
+ * time part.
+ * Anything else is illegal and will be treated like a
+ * traditional postgresql interval.
+ */
+ if (!(field[0][0] == 'p' &&
+ ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
+ {
+ return -1;
+ }
+
+
+ /*
+ * If the first field is exactly 1 character ('P'), it starts
+ * with date elements. Otherwise it's two characters ('PT');
+ * indicating it starts with a time part.
+ */
+ datepart = (field[0][1] == 0);
+
+ /*
+ * Every value must have a unit, so we require an even
+ * number of value/unit pairs. Therefore we require an
+ * odd nubmer of fields, including the prefix 'P'.
+ */
+ if ((nf & 1) == 0)
+ return -1;
+
+ /*
+ * Process pairs of fields at a time.
+ */
+ for (arg = 1 ; arg < nf ; arg+=2)
+ {
+ char * value = field[arg ];
+ char * units = field[arg+1];
+
+ /*
+ * The value part must be a number.
+ */
+ if (ftype[arg] != DTK_NUMBER)
+ return -1;
+
+ /*
+ * extract the number, almost exactly like the non-ISO interval.
+ */
+ val = strtol(value, &cp, 10);
+
+ /*
+ * One difference from the normal postgresql interval below...
+ * ISO 8601 states that "Of these, the comma is the preferred
+ * sign" so I allow it here for locales that support it.
+ * Note: Perhaps the old-style interval code below should
+ * allow for this too, but I didn't want to risk backward
+ * compatability.
+ */
+ if (*cp == '.' || *cp == ',')
+ {
+ fval = strtod(cp, &cp);
+ if (*cp != '\0')
+ return -1;
+
+ if (val < 0)
+ fval = -(fval);
+ }
+ else if (*cp == '\0')
+ fval = 0;
+ else
+ return -1;
+
+
+ if (datepart)
+ {
+ /*
+ * All the 8601 unit specifiers are 1 character, but may
+ * be followed by a 'T' character if transitioning between
+ * the date part and the time part. If it's not either
+ * one character or two characters with the second being 't'
+ * it's an error.
+ */
+ if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
+ return -1;
+
+ if (units[1] == 't')
+ datepart = 0;
+
+ switch (units[0]) /* Y M D W */
+ {
+ case 'd':
+ tm->tm_mday += val;
+ if (fval != 0)
+ adjust_fval(fval,tm,fsec, 86400);
+ tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
+ break;
+
+ case 'w':
+ tm->tm_mday += val * 7;
+ if (fval != 0)
+ adjust_fval(fval,tm,fsec,7 * 86400);
+ tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
+ break;
+
+ case 'm':
+ tm->tm_mon += val;
+ if (fval != 0)
+ adjust_fval(fval,tm,fsec,30 * 86400);
+ tmask = DTK_M(MONTH);
+ break;
+
+ case 'y':
+ /*
+ * Why can fractional months produce seconds,
+ * but fractional years can't? Well the older
+ * interval code below has the same property
+ * so this one follows the other one too.
+ */
+ tm->tm_year += val;
+ if (fval != 0)
+ tm->tm_mon += (fval * 12);
+ tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
+ break;
+
+ default:
+ return -1; /* invald date unit prefix */
+ }
+ }
+ else
+ {
+ /*
+ * ISO 8601 time part.
+ * In the time part, only one-character
+ * unit prefixes are allowed. If it's more
+ * than one character, it's not a valid ISO 8601
+ * time interval by duration.
+ */
+ if (units[1] != 0)
+ return -1;
+
+ switch (units[0]) /* H M S */
+ {
+ case 's':
+ tm->tm_sec += val;
+#ifdef HAVE_INT64_TIMESTAMP
+ *fsec += (fval * 1000000);
+#else
+ *fsec += fval;
+#endif
+ tmask = DTK_M(SECOND);
+ break;
+
+ case 'm':
+ tm->tm_min += val;
+ if (fval != 0)
+ adjust_fval(fval,tm,fsec,60);
+ tmask = DTK_M(MINUTE);
+ break;
+
+ case 'h':
+ tm->tm_hour += val;
+ if (fval != 0)
+ adjust_fval(fval,tm,fsec,3600);
+ tmask = DTK_M(HOUR);
+ break;
+
+ default:
+ return -1; /* invald time unit prefix */
+ }
+ }
+ fmask |= tmask;
+ }
+
+ if (*fsec != 0)
+ {
+ int sec;
+
+#ifdef HAVE_INT64_TIMESTAMP
+ sec = (*fsec / INT64CONST(1000000));
+ *fsec -= (sec * INT64CONST(1000000));
+#else
+ TMODULO(*fsec, sec, 1e0);
+#endif
+ tm->tm_sec += sec;
+ }
+ return (fmask != 0) ? 0 : -1;
+}
+
+
/* DecodeInterval()
* Interpret previously parsed fields for general time interval.
* Returns 0 if successful, DTERR code if bogus input detected.
*
* Allow "date" field DTK_DATE since this could be just
* an unsigned floating point number. - thomas 1997-11-16
*
* Allow ISO-style time span, with implicit units on number of days
* preceding an hh:mm:ss field. - thomas 1998-04-30
+ *
+ * Allow ISO-8601 style "Representation of time-interval by duration only"
+ * of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
*/
+
int
DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
{
int is_before = FALSE;
char *cp;
int fmask = 0,
tmask,
type;
int i;
int dterr;
@@ -2906,20 +3148,37 @@
type = IGNORE_DTF;
tm->tm_year = 0;
tm->tm_mon = 0;
tm->tm_mday = 0;
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
*fsec = 0;
+ /*
+ * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
+ * time-interval by duration only."
+ * Basic extended format: PnYnMnDTnHnMnS
+ * PnW
+ * http://www.astroclark.freeserve.co.uk/iso8601/index.html
+ * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
+ * Examples: P1D for 1 day
+ * PT1H for 1 hour
+ * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+ *
+ * The first field is exactly "p" or "pt" it may be of this type.
+ */
+ if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
+ return 0;
+ }
+
/* read through list backwards to pick up units before values */
for (i = nf - 1; i >= 0; i--)
{
switch (ftype[i])
{
case DTK_TIME:
dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
if (dterr)
return dterr;
type = DTK_DAY;
@@ -2983,20 +3242,21 @@
}
/* DROP THROUGH */
case DTK_DATE:
case DTK_NUMBER:
val = strtol(field[i], &cp, 10);
if (type == IGNORE_DTF)
type = DTK_SECOND;
+ /* should this allow ',' for locales that use it ? */
if (*cp == '.')
{
fval = strtod(cp, &cp);
if (*cp != '\0')
return DTERR_BAD_FORMAT;
if (val < 0)
fval = -(fval);
}
else if (*cp == '\0')
===================================================================
"Ron Mayer" <ron@intervideo.com> writes:
> Compared to the ISO 8601 time interval specification, the
> postgresql interval syntax is quite verbose. For example:
> Postgresql interval: ISO8601 Interval
> ---------------------------------------------------
> '1 year 6 months' 'P1Y6M'
> '3 hours 25 minutes 42 seconds' 'PT3H25M42S'
Er, don't we support that already? I know I saw code to support
something much like that syntax last time I looked into the datetime
routines.
regards, tom lane
Is there a way of producing as well as reading this format? Or did I miss
something?
cheers
andrew
Ron Mayer said:
> Short summary:
>
> This patch allows ISO 8601 "time intervals" using the "format
> with time-unit designators" to specify postgresql "intervals".
>
> Below I have (A) What these time intervals are, (B) What I
> modified to support them, (C) Issues with intervals I want
> to bring up, and (D) a patch supporting them.
>
> It's helpful to me. Any feedback is appreciated. If you
> did want to consider including it, let me know what to clean
> up. If not, I thought I'd just put it here if anyone else finds it
> useful too.
>
> Thanks for your time,
>
> Ron Mayer
>
> Longer:
>
> (A) What these intervals are.
>
> ISO 8601, the standard from which PostgreSQL gets some of it's time
> syntax, also has a specification for "time-intervals".
>
> In particular, section 5.5.4.2 has a "Representation of
> time-interval by duration only" which I believe maps
> nicely to ISO intervals.
>
> Compared to the ISO 8601 time interval specification, the
> postgresql interval syntax is quite verbose. For example:
>
> Postgresql interval: ISO8601 Interval
> ---------------------------------------------------
> '1 year 6 months' 'P1Y6M'
> '3 hours 25 minutes 42 seconds' 'PT3H25M42S'
>
> Yeah, it's uglier, but it sure is short which can make
> for quicker typing and shorter scripts, and if for some
> strange reason you had an application using this format
> it's nice not to have to translate.
>
> The syntax is as follows:
> Basic extended format: PnYnMnDTnHnMnS
> PnW
>
> Where everything before the "T" is a date-part and everything
> after is a time-part. W is for weeks.
> In the date-part, Y=Year, M=Month, D=Day
> In the time-part, H=Hour, M=Minute, S=Second
>
> Much more info can be found from the draft standard
> ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> The final standard's only available for $$$ so I didn't
> look at it. Some other sites imply that this part didn't
> change from the last draft to the standard.
>
>
> (B) This change was made by adding two functions to "datetime.c"
> next to where DecodeInterval parses the normal interval syntax.
>
> A total of 313 lines were added, including comments and sgml docs.
> Of these only 136 are actual code, the rest, comments, whitespace,
> etc.
>
>
> One new function "DecodeISO8601Interval" follows the style of
> "DecodeInterval" below it, and trys to strictly follow the ISO
> syntax. If it doesn't match, it'll return -1 and the old syntax
> will be checked as before.
>
> The first test (first character of the first field must be 'P', and
> second character must be 'T' or '\0') should be fast so I don't
> think this will impact performance of existing code.
>
>
> The second function ("adjust_fval") is just a small helper-function
> to remove some of the cut&paste style that DecodeInterval used.
>
> It seems to work.
>
=======================================================================
> betadb=# select 'P1M15DT12H30M7S'::interval;
> interval
> ------------------------
> 1 mon 15 days 12:30:07
> (1 row)
>
> betadb=# select '1 month 15 days 12 hours 30 minutes 7
> seconds'::interval;
> interval
> ------------------------
> 1 mon 15 days 12:30:07
> (1 row)
> =====================================================================
>
>
>
> (C) Open issues with intervals, and questions I'd like to ask.
>
> 1. DecodeInterval seems to have a hardcoded '.' for specifying
> fractional times. ISO 8601 states that both '.' and ',' are ok,
> but "of these, the comma is the preferred sign".
>
> In DecodeISO8601Interval I loosened the test to allow
> both but left it as it was in DecodeInterval. Should
> both be changed to make them more consistant?
>
> 2. In "DecodeInterval", fractional weeks and fractional months
> can produce seconds; but fractional years can not (rounded to
> months). I didn't understand the reasoning for this, so I left
> it the same, and followed the same convention for
> ISO intervals. Should I change this?
>
> 3. I could save a bunch of copy-paste-lines-of-code from the
> pre-existing DecodeInterval by calling the adjust_fval helper
> function. The tradeoff is a few extra function-calls when
> decoding an interval. However I didn't want to risk changes to
> the existing part unless you guys encourage me to do so.
>
>
> (D) The patch.
>
>
[snip]
Tom wrote:
> "Ron Mayer" <ron@intervideo.com> writes:
> > Compared to the ISO 8601 time interval specification, the
> > postgresql interval syntax is quite verbose. For example:
>
> > Postgresql interval: ISO8601 Interval
> > ---------------------------------------------------
> > '1 year 6 months' 'P1Y6M'
> > '3 hours 25 minutes 42 seconds' 'PT3H25M42S'
>
> Er, don't we support that already? I know I saw code to support
> something much like that syntax last time I looked into the datetime
> routines.
>
Nope.
Postgresql supports a rather bizzare shorthand that has a similar
syntax, but AFAICT, doesn't match ISO 8601 in any way that makes
it practical.
A disclaimer, I have the "Final Draft" (ISO/TC 154N 362
of 2000-12-19) of the spec; but have not seen the official,
expensive, version.
ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
For example, if I read it right, I have differences
like this:
Interval ISO Postgres
8601 shorthand
-----------------------------------------------------
'1 year 1 minute' 'P1YT1M' '1Y1M'
'1 year 1 month' 'P1Y1M' N/A
The best part about the postgresql syntax is that
they omit the required 'P', so it's easy to differentiate
between the two. :-)
Perhaps one could argue that the postgres shorthand should
follow the ISO conventions, but I'd not want to break backward
compatability, incase someone out there is using '1H30M' and
expecting minutes instead of months. If we didn't want to
support two syntaxes, I wouldn't mind eventually depricating
the less-standard one.
Ron
andrew@dunslane.net wrote:
>
> Is there a way of producing as well as reading this format? Or did I miss
> something?
Not yet, but I'd be happy to add it.
My immediate problem was having some 'P1Y6M' intervals to load.
I posted this much largely because it was useful to me so might
help others, and to see if it was of interest to others and
get feedback on what else to change.
I'd be happy to make it produce the output, and have some style
questions for doing so.
I'd hate to trigger this output on the already-existing 'datestyle'
of 'ISO', since that would break backward compatability.
I do notice that 8601 has both "basic" and "extended" formats.
The "basic format" is more terse ('19980115' instead of '1998-01-15').
Would it be useful if I added a 'datestyle' of 'ISO basic' which
would produce the most terse formats ('19980115' for dates,
and 'P1Y1M' for intervals)?
Ron
PS: What's the best inexpenive way for me to know if this changed
at all between the final draft and the published standard?
> Ron Mayer said:
> > This patch allows ISO 8601 "time intervals" using the "format
> > with time-unit designators" to specify postgresql "intervals".
> >...
> > Much more info can be found from the draft standard
> > ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> > The final standard's only available for $$$ so I didn't
> > look at it.
"Ron Mayer" <ron@intervideo.com> writes:
> Tom wrote:
>> Er, don't we support that already?
> Postgresql supports a rather bizzare shorthand that has a similar
> syntax, but AFAICT, doesn't match ISO 8601 in any way that makes
> it practical.
Well, it's *supposed* to match ISO, AFAICT (the comments in the code
talk about "ISO dates"). Unless ISO has put out multiple specs that
cover this?
> Perhaps one could argue that the postgres shorthand should
> follow the ISO conventions, but I'd not want to break backward
> compatability, incase someone out there is using '1H30M' and
> expecting minutes instead of months.
I doubt anyone is using it, because it's completely undocumented.
If we're going to support the real ISO spec, I'd suggest ripping
out any not-quite-there variant. (Especially so noting that your
code seems a lot cleaner than the ptype stuff.)
The datetime code is kind of a mess right now, because Thomas Lockhart
walked away from the project while only partway through some significant
additions. He left some incomplete features and quite a number of bugs
in new-and-untested code. We've been gradually cleaning up the problems,
but if if you find something that doesn't seem to make sense, it's
likely a bug rather than anything we want to preserve. In particular,
given the knowledge that it doesn't meet the ISO spec, I'd judge that
the existing code for the ISO shorthand was a work-in-progress.
regards, tom lane
"Ron Mayer" <ron@intervideo.com> writes:
> Would it be useful if I added a 'datestyle' of 'ISO basic' which
> would produce the most terse formats ('19980115' for dates,
> and 'P1Y1M' for intervals)?
I don't really care for using that name for it --- for one thing, you
couldn't do
set datestyle to iso basic;
because of syntax limitations. A one-word name is a much better idea.
Perhaps call it "compact" or "terse" datestyle?
> PS: What's the best inexpenive way for me to know if this changed
> at all between the final draft and the published standard?
ANSI sells PDFs of ISO specs at their online store
http://webstore.ansi.org/ansidocstore/default.asp
although it looks like they want $81 for 8601, which is not my idea of
"inexpensive".
Usually ISO final drafts differ very little from the published specs;
I think you could just work from the draft and no one would complain.
regards, tom lane
This has been saved for the 7.5 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
Feel free to submit an updated patch that rips out the old syntax, as
discussed, or replace this patch with a more comprehensive one.
---------------------------------------------------------------------------
Ron Mayer wrote:
> Short summary:
>
> This patch allows ISO 8601 "time intervals" using the "format
> with time-unit designators" to specify postgresql "intervals".
>
> Below I have (A) What these time intervals are, (B) What I
> modified to support them, (C) Issues with intervals I want
> to bring up, and (D) a patch supporting them.
>
> It's helpful to me. Any feedback is appreciated. If you
> did want to consider including it, let me know what to clean
> up. If not, I thought I'd just put it here if anyone else finds
> it useful too.
>
> Thanks for your time,
>
> Ron Mayer
>
> Longer:
>
> (A) What these intervals are.
>
> ISO 8601, the standard from which PostgreSQL gets some of it's
> time syntax, also has a specification for "time-intervals".
>
> In particular, section 5.5.4.2 has a "Representation of
> time-interval by duration only" which I believe maps
> nicely to ISO intervals.
>
> Compared to the ISO 8601 time interval specification, the
> postgresql interval syntax is quite verbose. For example:
>
> Postgresql interval: ISO8601 Interval
> ---------------------------------------------------
> '1 year 6 months' 'P1Y6M'
> '3 hours 25 minutes 42 seconds' 'PT3H25M42S'
>
> Yeah, it's uglier, but it sure is short which can make
> for quicker typing and shorter scripts, and if for some
> strange reason you had an application using this format
> it's nice not to have to translate.
>
> The syntax is as follows:
> Basic extended format: PnYnMnDTnHnMnS
> PnW
>
> Where everything before the "T" is a date-part and everything
> after is a time-part. W is for weeks.
> In the date-part, Y=Year, M=Month, D=Day
> In the time-part, H=Hour, M=Minute, S=Second
>
> Much more info can be found from the draft standard
> ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> The final standard's only available for $$$ so I didn't
> look at it. Some other sites imply that this part didn't
> change from the last draft to the standard.
>
>
> (B) This change was made by adding two functions to "datetime.c"
> next to where DecodeInterval parses the normal interval syntax.
>
> A total of 313 lines were added, including comments and sgml docs.
> Of these only 136 are actual code, the rest, comments, whitespace, etc.
>
>
> One new function "DecodeISO8601Interval" follows the style of
> "DecodeInterval" below it, and trys to strictly follow the ISO
> syntax. If it doesn't match, it'll return -1 and the old syntax
> will be checked as before.
>
> The first test (first character of the first field must be 'P',
> and second character must be 'T' or '\0') should be fast so I don't
> think this will impact performance of existing code.
>
>
> The second function ("adjust_fval") is just a small helper-function
> to remove some of the cut&paste style that DecodeInterval used.
>
> It seems to work.
> =======================================================================
> betadb=# select 'P1M15DT12H30M7S'::interval;
> interval
> ------------------------
> 1 mon 15 days 12:30:07
> (1 row)
>
> betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
> interval
> ------------------------
> 1 mon 15 days 12:30:07
> (1 row)
> =====================================================================
>
>
>
> (C) Open issues with intervals, and questions I'd like to ask.
>
> 1. DecodeInterval seems to have a hardcoded '.' for specifying
> fractional times. ISO 8601 states that both '.' and ',' are
> ok, but "of these, the comma is the preferred sign".
>
> In DecodeISO8601Interval I loosened the test to allow
> both but left it as it was in DecodeInterval. Should
> both be changed to make them more consistant?
>
> 2. In "DecodeInterval", fractional weeks and fractional months
> can produce seconds; but fractional years can not (rounded
> to months). I didn't understand the reasoning for this, so
> I left it the same, and followed the same convention for
> ISO intervals. Should I change this?
>
> 3. I could save a bunch of copy-paste-lines-of-code from the
> pre-existing DecodeInterval by calling the adjust_fval helper
> function. The tradeoff is a few extra function-calls when
> decoding an interval. However I didn't want to risk changes
> to the existing part unless you guys encourage me to do so.
>
>
> (D) The patch.
>
>
> Index: doc/src/sgml/datatype.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
> retrieving revision 1.123
> diff -u -1 -0 -r1.123 datatype.sgml
> --- doc/src/sgml/datatype.sgml 31 Aug 2003 17:32:18 -0000 1.123
> +++ doc/src/sgml/datatype.sgml 8 Sep 2003 04:04:58 -0000
> @@ -1735,20 +1735,71 @@
> Quantities of days, hours, minutes, and seconds can be specified without
> explicit unit markings. For example, <literal>'1 12:59:10'</> is read
> the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
> </para>
>
> <para>
> The optional precision
> <replaceable>p</replaceable> should be between 0 and 6, and
> defaults to the precision of the input literal.
> </para>
> +
> +
> + <para>
> + Alternatively, <type>interval</type> values can be written as
> + ISO 8601 time intervals, using the "Format with time-unit designators".
> + This format always starts with the character <literal>'P'</>, followed
> + by a string of values followed by single character time-unit designators.
> + A <literal>'T'</> separates the date and time parts of the interval.
> + </para>
> +
> + <para>
> + Format: PnYnMnDTnHnMnS
> + </para>
> + <para>
> + In this format, <literal>'n'</> gets replaced by a number, and
> + <literal>Y</> represents years,
> + <literal>M</> (in the date part) months,
> + <literal>D</> months,
> + <literal>H</> hours,
> + <literal>M</> (in the time part) minutes,
> + and <literal>S</> seconds.
> + </para>
> +
> +
> + <table id="interval-example-table">
> + <title>Interval Example</title>
> + <tgroup cols="2">
> + <thead>
> + <row>
> + <entry>Traditional</entry>
> + <entry>ISO-8601 time-interval</entry>
> + </row>
> + </thead>
> + <tbody>
> + <row>
> + <entry>1 month</entry>
> + <entry>P1M</entry>
> + </row>
> + <row>
> + <entry>1 hour 30 minutes</entry>
> + <entry>PT1H30M</entry>
> + </row>
> + <row>
> + <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
> + <entry>P2Y10M15DT10H30M20S</entry>
> + </row>
> + </tbody>
> + </thead>
> + </table>
> +
> + </para>
> </sect3>
>
> <sect3>
> <title>Special Values</title>
>
> <indexterm>
> <primary>time</primary>
> <secondary>constants</secondary>
> </indexterm>
>
> Index: src/backend/utils/adt/datetime.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
> retrieving revision 1.116
> diff -u -1 -0 -r1.116 datetime.c
> --- src/backend/utils/adt/datetime.c 27 Aug 2003 23:29:28 -0000 1.116
> +++ src/backend/utils/adt/datetime.c 8 Sep 2003 04:04:59 -0000
> @@ -30,20 +30,21 @@
> struct tm * tm, fsec_t *fsec, int *is2digits);
> static int DecodeNumberField(int len, char *str,
> int fmask, int *tmask,
> struct tm * tm, fsec_t *fsec, int *is2digits);
> static int DecodeTime(char *str, int fmask, int *tmask,
> struct tm * tm, fsec_t *fsec);
> static int DecodeTimezone(char *str, int *tzp);
> static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
> static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
> static void TrimTrailingZeros(char *str);
> +static int DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
>
>
> int day_tab[2][13] = {
> {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
> {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
>
> char *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
> "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
>
> char *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
> @@ -2872,30 +2873,271 @@
> default:
> *val = tp->value;
> break;
> }
> }
>
> return type;
> }
>
>
> +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
> +{
> + int sec;
> + fval *= scale;
> + sec = fval;
> + tm->tm_sec += sec;
> +#ifdef HAVE_INT64_TIMESTAMP
> + *fsec += ((fval - sec) * 1000000);
> +#else
> + *fsec += (fval - sec);
> +#endif
> +}
> +
> +
> +/* DecodeISO8601Interval()
> + *
> + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> + * time-interval by duration only."
> + * Basic extended format: PnYnMnDTnHnMnS
> + * PnW
> + * For more info.
> + * http://www.astroclark.freeserve.co.uk/iso8601/index.html
> + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> + *
> + * Examples: P1D for 1 day
> + * PT1H for 1 hour
> + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> + *
> + * The first field is exactly "p" or "pt" it may be of this type.
> + *
> + * Returns -1 if the field is not of this type.
> + *
> + * It pretty strictly checks the spec, with the two exceptions
> + * that a week field ('W') may coexist with other units, and that
> + * this function allows decimals in fields other than the least
> + * significant units.
> + */
> +int
> +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> +{
> + char *cp;
> + int fmask = 0,
> + tmask;
> + int val;
> + double fval;
> + int arg;
> + int datepart;
> +
> + /*
> + * An ISO 8601 "time-interval by duration only" must start
> + * with a 'P'. If it contains a date-part, 'p' will be the
> + * only character in the field. If it contains no date part
> + * it will contain exactly to characters 'PT' indicating a
> + * time part.
> + * Anything else is illegal and will be treated like a
> + * traditional postgresql interval.
> + */
> + if (!(field[0][0] == 'p' &&
> + ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
> + {
> + return -1;
> + }
> +
> +
> + /*
> + * If the first field is exactly 1 character ('P'), it starts
> + * with date elements. Otherwise it's two characters ('PT');
> + * indicating it starts with a time part.
> + */
> + datepart = (field[0][1] == 0);
> +
> + /*
> + * Every value must have a unit, so we require an even
> + * number of value/unit pairs. Therefore we require an
> + * odd nubmer of fields, including the prefix 'P'.
> + */
> + if ((nf & 1) == 0)
> + return -1;
> +
> + /*
> + * Process pairs of fields at a time.
> + */
> + for (arg = 1 ; arg < nf ; arg+=2)
> + {
> + char * value = field[arg ];
> + char * units = field[arg+1];
> +
> + /*
> + * The value part must be a number.
> + */
> + if (ftype[arg] != DTK_NUMBER)
> + return -1;
> +
> + /*
> + * extract the number, almost exactly like the non-ISO interval.
> + */
> + val = strtol(value, &cp, 10);
> +
> + /*
> + * One difference from the normal postgresql interval below...
> + * ISO 8601 states that "Of these, the comma is the preferred
> + * sign" so I allow it here for locales that support it.
> + * Note: Perhaps the old-style interval code below should
> + * allow for this too, but I didn't want to risk backward
> + * compatability.
> + */
> + if (*cp == '.' || *cp == ',')
> + {
> + fval = strtod(cp, &cp);
> + if (*cp != '\0')
> + return -1;
> +
> + if (val < 0)
> + fval = -(fval);
> + }
> + else if (*cp == '\0')
> + fval = 0;
> + else
> + return -1;
> +
> +
> + if (datepart)
> + {
> + /*
> + * All the 8601 unit specifiers are 1 character, but may
> + * be followed by a 'T' character if transitioning between
> + * the date part and the time part. If it's not either
> + * one character or two characters with the second being 't'
> + * it's an error.
> + */
> + if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
> + return -1;
> +
> + if (units[1] == 't')
> + datepart = 0;
> +
> + switch (units[0]) /* Y M D W */
> + {
> + case 'd':
> + tm->tm_mday += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec, 86400);
> + tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> + break;
> +
> + case 'w':
> + tm->tm_mday += val * 7;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,7 * 86400);
> + tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> + break;
> +
> + case 'm':
> + tm->tm_mon += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,30 * 86400);
> + tmask = DTK_M(MONTH);
> + break;
> +
> + case 'y':
> + /*
> + * Why can fractional months produce seconds,
> + * but fractional years can't? Well the older
> + * interval code below has the same property
> + * so this one follows the other one too.
> + */
> + tm->tm_year += val;
> + if (fval != 0)
> + tm->tm_mon += (fval * 12);
> + tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
> + break;
> +
> + default:
> + return -1; /* invald date unit prefix */
> + }
> + }
> + else
> + {
> + /*
> + * ISO 8601 time part.
> + * In the time part, only one-character
> + * unit prefixes are allowed. If it's more
> + * than one character, it's not a valid ISO 8601
> + * time interval by duration.
> + */
> + if (units[1] != 0)
> + return -1;
> +
> + switch (units[0]) /* H M S */
> + {
> + case 's':
> + tm->tm_sec += val;
> +#ifdef HAVE_INT64_TIMESTAMP
> + *fsec += (fval * 1000000);
> +#else
> + *fsec += fval;
> +#endif
> + tmask = DTK_M(SECOND);
> + break;
> +
> + case 'm':
> + tm->tm_min += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,60);
> + tmask = DTK_M(MINUTE);
> + break;
> +
> + case 'h':
> + tm->tm_hour += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,3600);
> + tmask = DTK_M(HOUR);
> + break;
> +
> + default:
> + return -1; /* invald time unit prefix */
> + }
> + }
> + fmask |= tmask;
> + }
> +
> + if (*fsec != 0)
> + {
> + int sec;
> +
> +#ifdef HAVE_INT64_TIMESTAMP
> + sec = (*fsec / INT64CONST(1000000));
> + *fsec -= (sec * INT64CONST(1000000));
> +#else
> + TMODULO(*fsec, sec, 1e0);
> +#endif
> + tm->tm_sec += sec;
> + }
> + return (fmask != 0) ? 0 : -1;
> +}
> +
> +
> /* DecodeInterval()
> * Interpret previously parsed fields for general time interval.
> * Returns 0 if successful, DTERR code if bogus input detected.
> *
> * Allow "date" field DTK_DATE since this could be just
> * an unsigned floating point number. - thomas 1997-11-16
> *
> * Allow ISO-style time span, with implicit units on number of days
> * preceding an hh:mm:ss field. - thomas 1998-04-30
> + *
> + * Allow ISO-8601 style "Representation of time-interval by duration only"
> + * of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
> */
> +
> int
> DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> {
> int is_before = FALSE;
> char *cp;
> int fmask = 0,
> tmask,
> type;
> int i;
> int dterr;
> @@ -2906,20 +3148,37 @@
>
> type = IGNORE_DTF;
> tm->tm_year = 0;
> tm->tm_mon = 0;
> tm->tm_mday = 0;
> tm->tm_hour = 0;
> tm->tm_min = 0;
> tm->tm_sec = 0;
> *fsec = 0;
>
> + /*
> + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> + * time-interval by duration only."
> + * Basic extended format: PnYnMnDTnHnMnS
> + * PnW
> + * http://www.astroclark.freeserve.co.uk/iso8601/index.html
> + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> + * Examples: P1D for 1 day
> + * PT1H for 1 hour
> + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> + *
> + * The first field is exactly "p" or "pt" it may be of this type.
> + */
> + if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
> + return 0;
> + }
> +
> /* read through list backwards to pick up units before values */
> for (i = nf - 1; i >= 0; i--)
> {
> switch (ftype[i])
> {
> case DTK_TIME:
> dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
> if (dterr)
> return dterr;
> type = DTK_DAY;
> @@ -2983,20 +3242,21 @@
> }
> /* DROP THROUGH */
>
> case DTK_DATE:
> case DTK_NUMBER:
> val = strtol(field[i], &cp, 10);
>
> if (type == IGNORE_DTF)
> type = DTK_SECOND;
>
> + /* should this allow ',' for locales that use it ? */
> if (*cp == '.')
> {
> fval = strtod(cp, &cp);
> if (*cp != '\0')
> return DTERR_BAD_FORMAT;
>
> if (val < 0)
> fval = -(fval);
> }
> else if (*cp == '\0')
>
> ===================================================================
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote: > > Perhaps one could argue that the postgres shorthand should > > follow the ISO conventions, but I'd not want to break backward > > compatability, incase someone out there is using '1H30M' and > > expecting minutes instead of months. > > I doubt anyone is using it, because it's completely undocumented. > If we're going to support the real ISO spec, I'd suggest ripping > out any not-quite-there variant. (Especially so noting that your > code seems a lot cleaner than the ptype stuff.) Agreed. Let me put your code in the queue for 7.5. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom wrote:
> "Ron Mayer" <ron@intervideo.com> writes:
> > Tom wrote:
> >> Er, don't we support that already?
> > ...AFAICT, doesn't match ISO 8601...
>
> Well, it's *supposed* to match ISO.... Unless ISO has put out
> multiple specs that cover this?
Any way to tell if this is the case.
8601's the one I see cited the most.
> > ...I'd not want to break backward compatability...'1H30M'
>
> I doubt anyone is using it, because it's completely undocumented.
> If we're going to support the real ISO spec, I'd suggest ripping
> out any not-quite-there variant.
I'm happy to look into it. Rip out completely? Ifdef?
> We've been gradually cleaning up the problems, but if if you find
> something that doesn't seem to make sense, it's likely a bug rather
> than anything we want to preserve.
I've seen a few more cases that don't make sense.
For example "why is 0.001 years less than 0.001 months".
betadb=# select '0.01 years'::interval
interval
----------
00:00:00
betadb=# select '0.01 months'::interval
interval
----------
07:12:00
If I'm breaking backward compatability anyway, I'd be happy to tweak
things like this one too. Unless, of course someone can give me a
reason why we want fractional years rounded to months, but fractional
months are rounded to fractions of a second.
Ron Mayer.
PS: mailinglist etiquite question... for discussion, should I
more this to hackers, or continue it here.
Ron Mayer wrote: > If I'm breaking backward compatability anyway, I'd be happy to tweak > things like this one too. Unless, of course someone can give me a > reason why we want fractional years rounded to months, but fractional > months are rounded to fractions of a second. > > Ron Mayer. > > PS: mailinglist etiquite question... for discussion, should I > more this to hackers, or continue it here. Your choice, but you get a larger audience on hackers. I usually keep things on patches when I have lots of code to post, and other times move to hackers. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Ron Mayer" <ron@intervideo.com> writes:
> For example "why is 0.001 years less than 0.001 months".
And look at this:
regression=# select '0.99 years'::interval;
interval
----------
11 mons
(1 row)
regression=# select '0.99 months'::interval;
interval
------------------
29 days 16:48:00
(1 row)
It kinda looks like fractional years are converted to integer months
(truncating) while fractional months are moved to the seconds part
of the interval. Ick. The handling ought to be consistent if you
ask me.
> If I'm breaking backward compatability anyway, I'd be happy to tweak
> things like this one too. Unless, of course someone can give me a
> reason why we want fractional years rounded to months, but fractional
> months are rounded to fractions of a second.
Actually, what I'd like to see done with interval is re-implement it as
a three-field entity, separately storing months, days, and seconds.
The separation between months and smaller units is good because a month
isn't a fixed number of any smaller unit, but the same holds true for
days and smaller units (days are not always 24 hours, consider DST
transitions). This would no doubt cause some backwards compatibility
problems, but overall it would fix many more cases than it breaks.
We see complaints about related issues regularly, every spring and fall...
I'm unsure whether fractional months or fractional days are sensible
to accept, but surely we should accept both or reject both. (This might
suggest that the underlying storage for the month and day fields should
be float not int, btw, but I am not sure about it.)
> PS: mailinglist etiquite question... for discussion, should I
> more this to hackers, or continue it here.
At this point it should move to pghackers, I think.
regards, tom lane
Tom Lane writes:
> "Ron Mayer" <ron@intervideo.com> writes:
> > Would it be useful if I added a 'datestyle' of 'ISO basic' which
> > would produce the most terse formats ('19980115' for dates,
> > and 'P1Y1M' for intervals)?
>
> I don't really care for using that name for it --- for one thing, you
> couldn't do
> set datestyle to iso basic;
> because of syntax limitations. A one-word name is a much better idea.
iso8601
Keep in mind that SQL itself is also a kind of ISO, so being more specific
is useful.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> I don't really care for using that name for it ---
> iso8601
> Keep in mind that SQL itself is also a kind of ISO, so being more specific
> is useful.
Yes, but by the same token "iso8601" isn't specific enough either.
Several of the other input formats we support have at least as good a
claim on that name.
regards, tom lane
Tom Lane writes: > Yes, but by the same token "iso8601" isn't specific enough either. > Several of the other input formats we support have at least as good a > claim on that name. The only input formats we support are along the lines of @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs @ 1 year 2 mons 3 days 04:05:06 These are also the supported output formats (the first you get for 'sql', 'postgres', and 'german' formats; the second is 'iso'). A quick check of ISO 8601 shows, however, that neither of these are close to anything specified in that standard. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Yes, but by the same token "iso8601" isn't specific enough either.
>> Several of the other input formats we support have at least as good a
>> claim on that name.
> The only input formats we support are along the lines of
> @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs
> @ 1 year 2 mons 3 days 04:05:06
Sorry, I was thinking of timestamp formats not intervals.
You're right that we don't have anything else particularly ISO-standard
for intervals, but my understanding is that formats like '2003-09-08
18:43:31.046283-04' are ISO8601 compatible for timestamps. (Possibly
you need to put a T in there for strict compatibility, not sure.)
regards, tom lane
Tom wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Tom Lane writes: > >> Yes, but by the same token "iso8601" isn't specific enough either. ISO 8601 gives more specific names. ISO 8601 Basic Format: P2Y10M15DT10H20M30S ISO 8601 Alternative Format: P00021015T102030 ISO 8601 Extended Format: P0002-10-15T10:20:30 In a way, the Extended Format is kinda nice, since its almost human readable. I could put in both the basic and extended ones, and call the dateformats iso8601basic and iso8601extended. The negative is that to do iso8601basic right, Id also have to tweak the date and time parts of the code too.
[ backtracking a little ]
"Ron Mayer" <ron@intervideo.com> writes:
> Tom wrote:
>> I doubt anyone is using it, because it's completely undocumented.
>> If we're going to support the real ISO spec, I'd suggest ripping
>> out any not-quite-there variant.
> I'm happy to look into it. Rip out completely? Ifdef?
"Rip" was what I had in mind --- the idea is to simplify the code,
which you will surely agree is too complicated as it stands. ifdefs
won't simplify the code or make it more understandable, rather the
reverse.
I have no problem with complex code when it's needed, but in this case
the ptype implementation of almost-ISO notation seems to me to affect
much more of the code than it has any right to on a usefulness basis.
regards, tom lane
Where did we leave this? --------------------------------------------------------------------------- Ron Mayer wrote: > > Tom wrote: > > Peter Eisentraut <peter_e@gmx.net> writes: > > > Tom Lane writes: > > >> Yes, but by the same token "iso8601" isn't specific enough either. > > ISO 8601 gives more specific names. > > ISO 8601 Basic Format: P2Y10M15DT10H20M30S > ISO 8601 Alternative Format: P00021015T102030 > ISO 8601 Extended Format: P0002-10-15T10:20:30 > > In a way, the Extended Format is kinda nice, since it?s > almost human readable. > > I could put in both the basic and extended ones, and > call the dateformats ?iso8601basic? and ?iso8601extended?. > The negative is that to do ?iso8601basic? right, I?d also > have to tweak the ?date? and ?time? parts of the code too. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Where did we leave this?
I thought it was proposed work for 7.5.
regards, tom lane
So far,
I have submitted the input-part.
I have a working output-part (attached below, but I'm
still cleaning up the documentation so I'll submit another
one later). The output is chosen by setting
the datestyle to 'iso8601basic'.
Those two changes don't break backward compatability
but don't fix too much odd behavior except ISO time interval I/O.
I was encouraged to look into changing the way timestamp
math is done (keeping month, and day, and second separate
until the end). This is a bigger change and I don't have
a stable version yet, and it breaks backward compatability.
I hope to submit a proposal for changes early enough in the
7.5 timeframe to submit fixes then as well. At the very least
I will fully document the existing interval-math as part of
this proposal so the docs can be updated even if the proposal
gets rejected.
Ron
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, September 26, 2003 3:54 PM
> To: Bruce Momjian
> Cc: Ron Mayer; Peter Eisentraut; andrew@dunslane.net;
> pgsql-patches@postgresql.org
> Subject: Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with
> time-unit deignators'
>
>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Where did we leave this?
>
> I thought it was proposed work for 7.5.
>
> regards, tom lane
>
>
Attachment
> I have a working output-part (attached below, but I'm > still cleaning up the documentation so I'll submit another > one later) Ugh. Something in this pc quoted some characters in the attachment. Rather than trying to apply it, wait a couple days and I'll submit an update where the docs match. Ron
Oh... and I'm pretty sure noone expects this before 7.5. :-) Looks like I'm the only one with input files that have these wierd-but-iso8601 'P1Y6M' and 'PT30M' inputs; so I can just use my own patch. :-) > > So far, > > I have submitted the input-part. > > I have a working output-part (attached below, but I'm > still cleaning up the documentation so I'll submit another > one later). The output is chosen by setting > the datestyle to 'iso8601basic'. > > Those two changes don't break backward compatability > but don't fix too much odd behavior except ISO time interval I/O. > > > I was encouraged to look into changing the way timestamp > math is done (keeping month, and day, and second separate > until the end). This is a bigger change and I don't have > a stable version yet, and it breaks backward compatability. > I hope to submit a proposal for changes early enough in the > 7.5 timeframe to submit fixes then as well. At the very least > I will fully document the existing interval-math as part of > this proposal so the docs can be updated even if the proposal > gets rejected. > > Ron > > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: Friday, September 26, 2003 3:54 PM > > To: Bruce Momjian > > Cc: Ron Mayer; Peter Eisentraut; andrew@dunslane.net; > > pgsql-patches@postgresql.org > > Subject: Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with > > time-unit deignators' > > > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Where did we leave this? > > > > I thought it was proposed work for 7.5. > > > > regards, tom lane > > > >
Is this ready for application? It looks good to me. However, there is
an "Open issues" section.
---------------------------------------------------------------------------
Ron Mayer wrote:
> Short summary:
>
> This patch allows ISO 8601 "time intervals" using the "format
> with time-unit designators" to specify postgresql "intervals".
>
> Below I have (A) What these time intervals are, (B) What I
> modified to support them, (C) Issues with intervals I want
> to bring up, and (D) a patch supporting them.
>
> It's helpful to me. Any feedback is appreciated. If you
> did want to consider including it, let me know what to clean
> up. If not, I thought I'd just put it here if anyone else finds
> it useful too.
>
> Thanks for your time,
>
> Ron Mayer
>
> Longer:
>
> (A) What these intervals are.
>
> ISO 8601, the standard from which PostgreSQL gets some of it's
> time syntax, also has a specification for "time-intervals".
>
> In particular, section 5.5.4.2 has a "Representation of
> time-interval by duration only" which I believe maps
> nicely to ISO intervals.
>
> Compared to the ISO 8601 time interval specification, the
> postgresql interval syntax is quite verbose. For example:
>
> Postgresql interval: ISO8601 Interval
> ---------------------------------------------------
> '1 year 6 months' 'P1Y6M'
> '3 hours 25 minutes 42 seconds' 'PT3H25M42S'
>
> Yeah, it's uglier, but it sure is short which can make
> for quicker typing and shorter scripts, and if for some
> strange reason you had an application using this format
> it's nice not to have to translate.
>
> The syntax is as follows:
> Basic extended format: PnYnMnDTnHnMnS
> PnW
>
> Where everything before the "T" is a date-part and everything
> after is a time-part. W is for weeks.
> In the date-part, Y=Year, M=Month, D=Day
> In the time-part, H=Hour, M=Minute, S=Second
>
> Much more info can be found from the draft standard
> ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> The final standard's only available for $$$ so I didn't
> look at it. Some other sites imply that this part didn't
> change from the last draft to the standard.
>
>
> (B) This change was made by adding two functions to "datetime.c"
> next to where DecodeInterval parses the normal interval syntax.
>
> A total of 313 lines were added, including comments and sgml docs.
> Of these only 136 are actual code, the rest, comments, whitespace, etc.
>
>
> One new function "DecodeISO8601Interval" follows the style of
> "DecodeInterval" below it, and trys to strictly follow the ISO
> syntax. If it doesn't match, it'll return -1 and the old syntax
> will be checked as before.
>
> The first test (first character of the first field must be 'P',
> and second character must be 'T' or '\0') should be fast so I don't
> think this will impact performance of existing code.
>
>
> The second function ("adjust_fval") is just a small helper-function
> to remove some of the cut&paste style that DecodeInterval used.
>
> It seems to work.
> =======================================================================
> betadb=# select 'P1M15DT12H30M7S'::interval;
> interval
> ------------------------
> 1 mon 15 days 12:30:07
> (1 row)
>
> betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
> interval
> ------------------------
> 1 mon 15 days 12:30:07
> (1 row)
> =====================================================================
>
>
>
> (C) Open issues with intervals, and questions I'd like to ask.
>
> 1. DecodeInterval seems to have a hardcoded '.' for specifying
> fractional times. ISO 8601 states that both '.' and ',' are
> ok, but "of these, the comma is the preferred sign".
>
> In DecodeISO8601Interval I loosened the test to allow
> both but left it as it was in DecodeInterval. Should
> both be changed to make them more consistant?
>
> 2. In "DecodeInterval", fractional weeks and fractional months
> can produce seconds; but fractional years can not (rounded
> to months). I didn't understand the reasoning for this, so
> I left it the same, and followed the same convention for
> ISO intervals. Should I change this?
>
> 3. I could save a bunch of copy-paste-lines-of-code from the
> pre-existing DecodeInterval by calling the adjust_fval helper
> function. The tradeoff is a few extra function-calls when
> decoding an interval. However I didn't want to risk changes
> to the existing part unless you guys encourage me to do so.
>
>
> (D) The patch.
>
>
> Index: doc/src/sgml/datatype.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
> retrieving revision 1.123
> diff -u -1 -0 -r1.123 datatype.sgml
> --- doc/src/sgml/datatype.sgml 31 Aug 2003 17:32:18 -0000 1.123
> +++ doc/src/sgml/datatype.sgml 8 Sep 2003 04:04:58 -0000
> @@ -1735,20 +1735,71 @@
> Quantities of days, hours, minutes, and seconds can be specified without
> explicit unit markings. For example, <literal>'1 12:59:10'</> is read
> the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
> </para>
>
> <para>
> The optional precision
> <replaceable>p</replaceable> should be between 0 and 6, and
> defaults to the precision of the input literal.
> </para>
> +
> +
> + <para>
> + Alternatively, <type>interval</type> values can be written as
> + ISO 8601 time intervals, using the "Format with time-unit designators".
> + This format always starts with the character <literal>'P'</>, followed
> + by a string of values followed by single character time-unit designators.
> + A <literal>'T'</> separates the date and time parts of the interval.
> + </para>
> +
> + <para>
> + Format: PnYnMnDTnHnMnS
> + </para>
> + <para>
> + In this format, <literal>'n'</> gets replaced by a number, and
> + <literal>Y</> represents years,
> + <literal>M</> (in the date part) months,
> + <literal>D</> months,
> + <literal>H</> hours,
> + <literal>M</> (in the time part) minutes,
> + and <literal>S</> seconds.
> + </para>
> +
> +
> + <table id="interval-example-table">
> + <title>Interval Example</title>
> + <tgroup cols="2">
> + <thead>
> + <row>
> + <entry>Traditional</entry>
> + <entry>ISO-8601 time-interval</entry>
> + </row>
> + </thead>
> + <tbody>
> + <row>
> + <entry>1 month</entry>
> + <entry>P1M</entry>
> + </row>
> + <row>
> + <entry>1 hour 30 minutes</entry>
> + <entry>PT1H30M</entry>
> + </row>
> + <row>
> + <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
> + <entry>P2Y10M15DT10H30M20S</entry>
> + </row>
> + </tbody>
> + </thead>
> + </table>
> +
> + </para>
> </sect3>
>
> <sect3>
> <title>Special Values</title>
>
> <indexterm>
> <primary>time</primary>
> <secondary>constants</secondary>
> </indexterm>
>
> Index: src/backend/utils/adt/datetime.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
> retrieving revision 1.116
> diff -u -1 -0 -r1.116 datetime.c
> --- src/backend/utils/adt/datetime.c 27 Aug 2003 23:29:28 -0000 1.116
> +++ src/backend/utils/adt/datetime.c 8 Sep 2003 04:04:59 -0000
> @@ -30,20 +30,21 @@
> struct tm * tm, fsec_t *fsec, int *is2digits);
> static int DecodeNumberField(int len, char *str,
> int fmask, int *tmask,
> struct tm * tm, fsec_t *fsec, int *is2digits);
> static int DecodeTime(char *str, int fmask, int *tmask,
> struct tm * tm, fsec_t *fsec);
> static int DecodeTimezone(char *str, int *tzp);
> static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
> static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
> static void TrimTrailingZeros(char *str);
> +static int DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
>
>
> int day_tab[2][13] = {
> {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
> {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
>
> char *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
> "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
>
> char *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
> @@ -2872,30 +2873,271 @@
> default:
> *val = tp->value;
> break;
> }
> }
>
> return type;
> }
>
>
> +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
> +{
> + int sec;
> + fval *= scale;
> + sec = fval;
> + tm->tm_sec += sec;
> +#ifdef HAVE_INT64_TIMESTAMP
> + *fsec += ((fval - sec) * 1000000);
> +#else
> + *fsec += (fval - sec);
> +#endif
> +}
> +
> +
> +/* DecodeISO8601Interval()
> + *
> + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> + * time-interval by duration only."
> + * Basic extended format: PnYnMnDTnHnMnS
> + * PnW
> + * For more info.
> + * http://www.astroclark.freeserve.co.uk/iso8601/index.html
> + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> + *
> + * Examples: P1D for 1 day
> + * PT1H for 1 hour
> + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> + *
> + * The first field is exactly "p" or "pt" it may be of this type.
> + *
> + * Returns -1 if the field is not of this type.
> + *
> + * It pretty strictly checks the spec, with the two exceptions
> + * that a week field ('W') may coexist with other units, and that
> + * this function allows decimals in fields other than the least
> + * significant units.
> + */
> +int
> +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> +{
> + char *cp;
> + int fmask = 0,
> + tmask;
> + int val;
> + double fval;
> + int arg;
> + int datepart;
> +
> + /*
> + * An ISO 8601 "time-interval by duration only" must start
> + * with a 'P'. If it contains a date-part, 'p' will be the
> + * only character in the field. If it contains no date part
> + * it will contain exactly to characters 'PT' indicating a
> + * time part.
> + * Anything else is illegal and will be treated like a
> + * traditional postgresql interval.
> + */
> + if (!(field[0][0] == 'p' &&
> + ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
> + {
> + return -1;
> + }
> +
> +
> + /*
> + * If the first field is exactly 1 character ('P'), it starts
> + * with date elements. Otherwise it's two characters ('PT');
> + * indicating it starts with a time part.
> + */
> + datepart = (field[0][1] == 0);
> +
> + /*
> + * Every value must have a unit, so we require an even
> + * number of value/unit pairs. Therefore we require an
> + * odd nubmer of fields, including the prefix 'P'.
> + */
> + if ((nf & 1) == 0)
> + return -1;
> +
> + /*
> + * Process pairs of fields at a time.
> + */
> + for (arg = 1 ; arg < nf ; arg+=2)
> + {
> + char * value = field[arg ];
> + char * units = field[arg+1];
> +
> + /*
> + * The value part must be a number.
> + */
> + if (ftype[arg] != DTK_NUMBER)
> + return -1;
> +
> + /*
> + * extract the number, almost exactly like the non-ISO interval.
> + */
> + val = strtol(value, &cp, 10);
> +
> + /*
> + * One difference from the normal postgresql interval below...
> + * ISO 8601 states that "Of these, the comma is the preferred
> + * sign" so I allow it here for locales that support it.
> + * Note: Perhaps the old-style interval code below should
> + * allow for this too, but I didn't want to risk backward
> + * compatability.
> + */
> + if (*cp == '.' || *cp == ',')
> + {
> + fval = strtod(cp, &cp);
> + if (*cp != '\0')
> + return -1;
> +
> + if (val < 0)
> + fval = -(fval);
> + }
> + else if (*cp == '\0')
> + fval = 0;
> + else
> + return -1;
> +
> +
> + if (datepart)
> + {
> + /*
> + * All the 8601 unit specifiers are 1 character, but may
> + * be followed by a 'T' character if transitioning between
> + * the date part and the time part. If it's not either
> + * one character or two characters with the second being 't'
> + * it's an error.
> + */
> + if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
> + return -1;
> +
> + if (units[1] == 't')
> + datepart = 0;
> +
> + switch (units[0]) /* Y M D W */
> + {
> + case 'd':
> + tm->tm_mday += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec, 86400);
> + tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> + break;
> +
> + case 'w':
> + tm->tm_mday += val * 7;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,7 * 86400);
> + tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> + break;
> +
> + case 'm':
> + tm->tm_mon += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,30 * 86400);
> + tmask = DTK_M(MONTH);
> + break;
> +
> + case 'y':
> + /*
> + * Why can fractional months produce seconds,
> + * but fractional years can't? Well the older
> + * interval code below has the same property
> + * so this one follows the other one too.
> + */
> + tm->tm_year += val;
> + if (fval != 0)
> + tm->tm_mon += (fval * 12);
> + tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
> + break;
> +
> + default:
> + return -1; /* invald date unit prefix */
> + }
> + }
> + else
> + {
> + /*
> + * ISO 8601 time part.
> + * In the time part, only one-character
> + * unit prefixes are allowed. If it's more
> + * than one character, it's not a valid ISO 8601
> + * time interval by duration.
> + */
> + if (units[1] != 0)
> + return -1;
> +
> + switch (units[0]) /* H M S */
> + {
> + case 's':
> + tm->tm_sec += val;
> +#ifdef HAVE_INT64_TIMESTAMP
> + *fsec += (fval * 1000000);
> +#else
> + *fsec += fval;
> +#endif
> + tmask = DTK_M(SECOND);
> + break;
> +
> + case 'm':
> + tm->tm_min += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,60);
> + tmask = DTK_M(MINUTE);
> + break;
> +
> + case 'h':
> + tm->tm_hour += val;
> + if (fval != 0)
> + adjust_fval(fval,tm,fsec,3600);
> + tmask = DTK_M(HOUR);
> + break;
> +
> + default:
> + return -1; /* invald time unit prefix */
> + }
> + }
> + fmask |= tmask;
> + }
> +
> + if (*fsec != 0)
> + {
> + int sec;
> +
> +#ifdef HAVE_INT64_TIMESTAMP
> + sec = (*fsec / INT64CONST(1000000));
> + *fsec -= (sec * INT64CONST(1000000));
> +#else
> + TMODULO(*fsec, sec, 1e0);
> +#endif
> + tm->tm_sec += sec;
> + }
> + return (fmask != 0) ? 0 : -1;
> +}
> +
> +
> /* DecodeInterval()
> * Interpret previously parsed fields for general time interval.
> * Returns 0 if successful, DTERR code if bogus input detected.
> *
> * Allow "date" field DTK_DATE since this could be just
> * an unsigned floating point number. - thomas 1997-11-16
> *
> * Allow ISO-style time span, with implicit units on number of days
> * preceding an hh:mm:ss field. - thomas 1998-04-30
> + *
> + * Allow ISO-8601 style "Representation of time-interval by duration only"
> + * of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
> */
> +
> int
> DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> {
> int is_before = FALSE;
> char *cp;
> int fmask = 0,
> tmask,
> type;
> int i;
> int dterr;
> @@ -2906,20 +3148,37 @@
>
> type = IGNORE_DTF;
> tm->tm_year = 0;
> tm->tm_mon = 0;
> tm->tm_mday = 0;
> tm->tm_hour = 0;
> tm->tm_min = 0;
> tm->tm_sec = 0;
> *fsec = 0;
>
> + /*
> + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> + * time-interval by duration only."
> + * Basic extended format: PnYnMnDTnHnMnS
> + * PnW
> + * http://www.astroclark.freeserve.co.uk/iso8601/index.html
> + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> + * Examples: P1D for 1 day
> + * PT1H for 1 hour
> + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> + *
> + * The first field is exactly "p" or "pt" it may be of this type.
> + */
> + if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
> + return 0;
> + }
> +
> /* read through list backwards to pick up units before values */
> for (i = nf - 1; i >= 0; i--)
> {
> switch (ftype[i])
> {
> case DTK_TIME:
> dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
> if (dterr)
> return dterr;
> type = DTK_DAY;
> @@ -2983,20 +3242,21 @@
> }
> /* DROP THROUGH */
>
> case DTK_DATE:
> case DTK_NUMBER:
> val = strtol(field[i], &cp, 10);
>
> if (type == IGNORE_DTF)
> type = DTK_SECOND;
>
> + /* should this allow ',' for locales that use it ? */
> if (*cp == '.')
> {
> fval = strtod(cp, &cp);
> if (*cp != '\0')
> return DTERR_BAD_FORMAT;
>
> if (val < 0)
> fval = -(fval);
> }
> else if (*cp == '\0')
>
> ===================================================================
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
And another open issues email. --------------------------------------------------------------------------- Ron Mayer wrote: > > Tom wrote: > > Peter Eisentraut <peter_e@gmx.net> writes: > > > Tom Lane writes: > > >> Yes, but by the same token "iso8601" isn't specific enough either. > > ISO 8601 gives more specific names. > > ISO 8601 Basic Format: P2Y10M15DT10H20M30S > ISO 8601 Alternative Format: P00021015T102030 > ISO 8601 Extended Format: P0002-10-15T10:20:30 > > In a way, the Extended Format is kinda nice, since it?s > almost human readable. > > I could put in both the basic and extended ones, and > call the dateformats ?iso8601basic? and ?iso8601extended?. > The negative is that to do ?iso8601basic? right, I?d also > have to tweak the ?date? and ?time? parts of the code too. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian writes:
> Is this ready for application? It looks good to me. However, there is
> an "Open issues" section.
It would be more useful to implement the SQL standard for intervals first
instead of inventing more nonstandard formats for it.
>
> ---------------------------------------------------------------------------
>
> Ron Mayer wrote:
> > Short summary:
> >
> > This patch allows ISO 8601 "time intervals" using the "format
> > with time-unit designators" to specify postgresql "intervals".
> >
> > Below I have (A) What these time intervals are, (B) What I
> > modified to support them, (C) Issues with intervals I want
> > to bring up, and (D) a patch supporting them.
> >
> > It's helpful to me. Any feedback is appreciated. If you
> > did want to consider including it, let me know what to clean
> > up. If not, I thought I'd just put it here if anyone else finds
> > it useful too.
> >
> > Thanks for your time,
> >
> > Ron Mayer
> >
> > Longer:
> >
> > (A) What these intervals are.
> >
> > ISO 8601, the standard from which PostgreSQL gets some of it's
> > time syntax, also has a specification for "time-intervals".
> >
> > In particular, section 5.5.4.2 has a "Representation of
> > time-interval by duration only" which I believe maps
> > nicely to ISO intervals.
> >
> > Compared to the ISO 8601 time interval specification, the
> > postgresql interval syntax is quite verbose. For example:
> >
> > Postgresql interval: ISO8601 Interval
> > ---------------------------------------------------
> > '1 year 6 months' 'P1Y6M'
> > '3 hours 25 minutes 42 seconds' 'PT3H25M42S'
> >
> > Yeah, it's uglier, but it sure is short which can make
> > for quicker typing and shorter scripts, and if for some
> > strange reason you had an application using this format
> > it's nice not to have to translate.
> >
> > The syntax is as follows:
> > Basic extended format: PnYnMnDTnHnMnS
> > PnW
> >
> > Where everything before the "T" is a date-part and everything
> > after is a time-part. W is for weeks.
> > In the date-part, Y=Year, M=Month, D=Day
> > In the time-part, H=Hour, M=Minute, S=Second
> >
> > Much more info can be found from the draft standard
> > ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> > The final standard's only available for $$$ so I didn't
> > look at it. Some other sites imply that this part didn't
> > change from the last draft to the standard.
> >
> >
> > (B) This change was made by adding two functions to "datetime.c"
> > next to where DecodeInterval parses the normal interval syntax.
> >
> > A total of 313 lines were added, including comments and sgml docs.
> > Of these only 136 are actual code, the rest, comments, whitespace, etc.
> >
> >
> > One new function "DecodeISO8601Interval" follows the style of
> > "DecodeInterval" below it, and trys to strictly follow the ISO
> > syntax. If it doesn't match, it'll return -1 and the old syntax
> > will be checked as before.
> >
> > The first test (first character of the first field must be 'P',
> > and second character must be 'T' or '\0') should be fast so I don't
> > think this will impact performance of existing code.
> >
> >
> > The second function ("adjust_fval") is just a small helper-function
> > to remove some of the cut&paste style that DecodeInterval used.
> >
> > It seems to work.
> > =======================================================================
> > betadb=# select 'P1M15DT12H30M7S'::interval;
> > interval
> > ------------------------
> > 1 mon 15 days 12:30:07
> > (1 row)
> >
> > betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
> > interval
> > ------------------------
> > 1 mon 15 days 12:30:07
> > (1 row)
> > =====================================================================
> >
> >
> >
> > (C) Open issues with intervals, and questions I'd like to ask.
> >
> > 1. DecodeInterval seems to have a hardcoded '.' for specifying
> > fractional times. ISO 8601 states that both '.' and ',' are
> > ok, but "of these, the comma is the preferred sign".
> >
> > In DecodeISO8601Interval I loosened the test to allow
> > both but left it as it was in DecodeInterval. Should
> > both be changed to make them more consistant?
> >
> > 2. In "DecodeInterval", fractional weeks and fractional months
> > can produce seconds; but fractional years can not (rounded
> > to months). I didn't understand the reasoning for this, so
> > I left it the same, and followed the same convention for
> > ISO intervals. Should I change this?
> >
> > 3. I could save a bunch of copy-paste-lines-of-code from the
> > pre-existing DecodeInterval by calling the adjust_fval helper
> > function. The tradeoff is a few extra function-calls when
> > decoding an interval. However I didn't want to risk changes
> > to the existing part unless you guys encourage me to do so.
> >
> >
> > (D) The patch.
> >
> >
> > Index: doc/src/sgml/datatype.sgml
> > ===================================================================
> > RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
> > retrieving revision 1.123
> > diff -u -1 -0 -r1.123 datatype.sgml
> > --- doc/src/sgml/datatype.sgml 31 Aug 2003 17:32:18 -0000 1.123
> > +++ doc/src/sgml/datatype.sgml 8 Sep 2003 04:04:58 -0000
> > @@ -1735,20 +1735,71 @@
> > Quantities of days, hours, minutes, and seconds can be specified without
> > explicit unit markings. For example, <literal>'1 12:59:10'</> is read
> > the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
> > </para>
> >
> > <para>
> > The optional precision
> > <replaceable>p</replaceable> should be between 0 and 6, and
> > defaults to the precision of the input literal.
> > </para>
> > +
> > +
> > + <para>
> > + Alternatively, <type>interval</type> values can be written as
> > + ISO 8601 time intervals, using the "Format with time-unit designators".
> > + This format always starts with the character <literal>'P'</>, followed
> > + by a string of values followed by single character time-unit designators.
> > + A <literal>'T'</> separates the date and time parts of the interval.
> > + </para>
> > +
> > + <para>
> > + Format: PnYnMnDTnHnMnS
> > + </para>
> > + <para>
> > + In this format, <literal>'n'</> gets replaced by a number, and
> > + <literal>Y</> represents years,
> > + <literal>M</> (in the date part) months,
> > + <literal>D</> months,
> > + <literal>H</> hours,
> > + <literal>M</> (in the time part) minutes,
> > + and <literal>S</> seconds.
> > + </para>
> > +
> > +
> > + <table id="interval-example-table">
> > + <title>Interval Example</title>
> > + <tgroup cols="2">
> > + <thead>
> > + <row>
> > + <entry>Traditional</entry>
> > + <entry>ISO-8601 time-interval</entry>
> > + </row>
> > + </thead>
> > + <tbody>
> > + <row>
> > + <entry>1 month</entry>
> > + <entry>P1M</entry>
> > + </row>
> > + <row>
> > + <entry>1 hour 30 minutes</entry>
> > + <entry>PT1H30M</entry>
> > + </row>
> > + <row>
> > + <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
> > + <entry>P2Y10M15DT10H30M20S</entry>
> > + </row>
> > + </tbody>
> > + </thead>
> > + </table>
> > +
> > + </para>
> > </sect3>
> >
> > <sect3>
> > <title>Special Values</title>
> >
> > <indexterm>
> > <primary>time</primary>
> > <secondary>constants</secondary>
> > </indexterm>
> >
> > Index: src/backend/utils/adt/datetime.c
> > ===================================================================
> > RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
> > retrieving revision 1.116
> > diff -u -1 -0 -r1.116 datetime.c
> > --- src/backend/utils/adt/datetime.c 27 Aug 2003 23:29:28 -0000 1.116
> > +++ src/backend/utils/adt/datetime.c 8 Sep 2003 04:04:59 -0000
> > @@ -30,20 +30,21 @@
> > struct tm * tm, fsec_t *fsec, int *is2digits);
> > static int DecodeNumberField(int len, char *str,
> > int fmask, int *tmask,
> > struct tm * tm, fsec_t *fsec, int *is2digits);
> > static int DecodeTime(char *str, int fmask, int *tmask,
> > struct tm * tm, fsec_t *fsec);
> > static int DecodeTimezone(char *str, int *tzp);
> > static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
> > static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
> > static void TrimTrailingZeros(char *str);
> > +static int DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
> >
> >
> > int day_tab[2][13] = {
> > {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
> > {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
> >
> > char *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
> > "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
> >
> > char *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
> > @@ -2872,30 +2873,271 @@
> > default:
> > *val = tp->value;
> > break;
> > }
> > }
> >
> > return type;
> > }
> >
> >
> > +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
> > +{
> > + int sec;
> > + fval *= scale;
> > + sec = fval;
> > + tm->tm_sec += sec;
> > +#ifdef HAVE_INT64_TIMESTAMP
> > + *fsec += ((fval - sec) * 1000000);
> > +#else
> > + *fsec += (fval - sec);
> > +#endif
> > +}
> > +
> > +
> > +/* DecodeISO8601Interval()
> > + *
> > + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> > + * time-interval by duration only."
> > + * Basic extended format: PnYnMnDTnHnMnS
> > + * PnW
> > + * For more info.
> > + * http://www.astroclark.freeserve.co.uk/iso8601/index.html
> > + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> > + *
> > + * Examples: P1D for 1 day
> > + * PT1H for 1 hour
> > + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> > + *
> > + * The first field is exactly "p" or "pt" it may be of this type.
> > + *
> > + * Returns -1 if the field is not of this type.
> > + *
> > + * It pretty strictly checks the spec, with the two exceptions
> > + * that a week field ('W') may coexist with other units, and that
> > + * this function allows decimals in fields other than the least
> > + * significant units.
> > + */
> > +int
> > +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> > +{
> > + char *cp;
> > + int fmask = 0,
> > + tmask;
> > + int val;
> > + double fval;
> > + int arg;
> > + int datepart;
> > +
> > + /*
> > + * An ISO 8601 "time-interval by duration only" must start
> > + * with a 'P'. If it contains a date-part, 'p' will be the
> > + * only character in the field. If it contains no date part
> > + * it will contain exactly to characters 'PT' indicating a
> > + * time part.
> > + * Anything else is illegal and will be treated like a
> > + * traditional postgresql interval.
> > + */
> > + if (!(field[0][0] == 'p' &&
> > + ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
> > + {
> > + return -1;
> > + }
> > +
> > +
> > + /*
> > + * If the first field is exactly 1 character ('P'), it starts
> > + * with date elements. Otherwise it's two characters ('PT');
> > + * indicating it starts with a time part.
> > + */
> > + datepart = (field[0][1] == 0);
> > +
> > + /*
> > + * Every value must have a unit, so we require an even
> > + * number of value/unit pairs. Therefore we require an
> > + * odd nubmer of fields, including the prefix 'P'.
> > + */
> > + if ((nf & 1) == 0)
> > + return -1;
> > +
> > + /*
> > + * Process pairs of fields at a time.
> > + */
> > + for (arg = 1 ; arg < nf ; arg+=2)
> > + {
> > + char * value = field[arg ];
> > + char * units = field[arg+1];
> > +
> > + /*
> > + * The value part must be a number.
> > + */
> > + if (ftype[arg] != DTK_NUMBER)
> > + return -1;
> > +
> > + /*
> > + * extract the number, almost exactly like the non-ISO interval.
> > + */
> > + val = strtol(value, &cp, 10);
> > +
> > + /*
> > + * One difference from the normal postgresql interval below...
> > + * ISO 8601 states that "Of these, the comma is the preferred
> > + * sign" so I allow it here for locales that support it.
> > + * Note: Perhaps the old-style interval code below should
> > + * allow for this too, but I didn't want to risk backward
> > + * compatability.
> > + */
> > + if (*cp == '.' || *cp == ',')
> > + {
> > + fval = strtod(cp, &cp);
> > + if (*cp != '\0')
> > + return -1;
> > +
> > + if (val < 0)
> > + fval = -(fval);
> > + }
> > + else if (*cp == '\0')
> > + fval = 0;
> > + else
> > + return -1;
> > +
> > +
> > + if (datepart)
> > + {
> > + /*
> > + * All the 8601 unit specifiers are 1 character, but may
> > + * be followed by a 'T' character if transitioning between
> > + * the date part and the time part. If it's not either
> > + * one character or two characters with the second being 't'
> > + * it's an error.
> > + */
> > + if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
> > + return -1;
> > +
> > + if (units[1] == 't')
> > + datepart = 0;
> > +
> > + switch (units[0]) /* Y M D W */
> > + {
> > + case 'd':
> > + tm->tm_mday += val;
> > + if (fval != 0)
> > + adjust_fval(fval,tm,fsec, 86400);
> > + tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> > + break;
> > +
> > + case 'w':
> > + tm->tm_mday += val * 7;
> > + if (fval != 0)
> > + adjust_fval(fval,tm,fsec,7 * 86400);
> > + tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> > + break;
> > +
> > + case 'm':
> > + tm->tm_mon += val;
> > + if (fval != 0)
> > + adjust_fval(fval,tm,fsec,30 * 86400);
> > + tmask = DTK_M(MONTH);
> > + break;
> > +
> > + case 'y':
> > + /*
> > + * Why can fractional months produce seconds,
> > + * but fractional years can't? Well the older
> > + * interval code below has the same property
> > + * so this one follows the other one too.
> > + */
> > + tm->tm_year += val;
> > + if (fval != 0)
> > + tm->tm_mon += (fval * 12);
> > + tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
> > + break;
> > +
> > + default:
> > + return -1; /* invald date unit prefix */
> > + }
> > + }
> > + else
> > + {
> > + /*
> > + * ISO 8601 time part.
> > + * In the time part, only one-character
> > + * unit prefixes are allowed. If it's more
> > + * than one character, it's not a valid ISO 8601
> > + * time interval by duration.
> > + */
> > + if (units[1] != 0)
> > + return -1;
> > +
> > + switch (units[0]) /* H M S */
> > + {
> > + case 's':
> > + tm->tm_sec += val;
> > +#ifdef HAVE_INT64_TIMESTAMP
> > + *fsec += (fval * 1000000);
> > +#else
> > + *fsec += fval;
> > +#endif
> > + tmask = DTK_M(SECOND);
> > + break;
> > +
> > + case 'm':
> > + tm->tm_min += val;
> > + if (fval != 0)
> > + adjust_fval(fval,tm,fsec,60);
> > + tmask = DTK_M(MINUTE);
> > + break;
> > +
> > + case 'h':
> > + tm->tm_hour += val;
> > + if (fval != 0)
> > + adjust_fval(fval,tm,fsec,3600);
> > + tmask = DTK_M(HOUR);
> > + break;
> > +
> > + default:
> > + return -1; /* invald time unit prefix */
> > + }
> > + }
> > + fmask |= tmask;
> > + }
> > +
> > + if (*fsec != 0)
> > + {
> > + int sec;
> > +
> > +#ifdef HAVE_INT64_TIMESTAMP
> > + sec = (*fsec / INT64CONST(1000000));
> > + *fsec -= (sec * INT64CONST(1000000));
> > +#else
> > + TMODULO(*fsec, sec, 1e0);
> > +#endif
> > + tm->tm_sec += sec;
> > + }
> > + return (fmask != 0) ? 0 : -1;
> > +}
> > +
> > +
> > /* DecodeInterval()
> > * Interpret previously parsed fields for general time interval.
> > * Returns 0 if successful, DTERR code if bogus input detected.
> > *
> > * Allow "date" field DTK_DATE since this could be just
> > * an unsigned floating point number. - thomas 1997-11-16
> > *
> > * Allow ISO-style time span, with implicit units on number of days
> > * preceding an hh:mm:ss field. - thomas 1998-04-30
> > + *
> > + * Allow ISO-8601 style "Representation of time-interval by duration only"
> > + * of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
> > */
> > +
> > int
> > DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> > {
> > int is_before = FALSE;
> > char *cp;
> > int fmask = 0,
> > tmask,
> > type;
> > int i;
> > int dterr;
> > @@ -2906,20 +3148,37 @@
> >
> > type = IGNORE_DTF;
> > tm->tm_year = 0;
> > tm->tm_mon = 0;
> > tm->tm_mday = 0;
> > tm->tm_hour = 0;
> > tm->tm_min = 0;
> > tm->tm_sec = 0;
> > *fsec = 0;
> >
> > + /*
> > + * Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> > + * time-interval by duration only."
> > + * Basic extended format: PnYnMnDTnHnMnS
> > + * PnW
> > + * http://www.astroclark.freeserve.co.uk/iso8601/index.html
> > + * ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> > + * Examples: P1D for 1 day
> > + * PT1H for 1 hour
> > + * P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> > + *
> > + * The first field is exactly "p" or "pt" it may be of this type.
> > + */
> > + if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
> > + return 0;
> > + }
> > +
> > /* read through list backwards to pick up units before values */
> > for (i = nf - 1; i >= 0; i--)
> > {
> > switch (ftype[i])
> > {
> > case DTK_TIME:
> > dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
> > if (dterr)
> > return dterr;
> > type = DTK_DAY;
> > @@ -2983,20 +3242,21 @@
> > }
> > /* DROP THROUGH */
> >
> > case DTK_DATE:
> > case DTK_NUMBER:
> > val = strtol(field[i], &cp, 10);
> >
> > if (type == IGNORE_DTF)
> > type = DTK_SECOND;
> >
> > + /* should this allow ',' for locales that use it ? */
> > if (*cp == '.')
> > {
> > fval = strtod(cp, &cp);
> > if (*cp != '\0')
> > return DTERR_BAD_FORMAT;
> >
> > if (val < 0)
> > fval = -(fval);
> > }
> > else if (*cp == '\0')
> >
> > ===================================================================
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > Bruce Momjian writes: > > > Is this ready for application? It looks good to me. However, there is > > an "Open issues" section. > > It would be more useful to implement the SQL standard for intervals first > instead of inventing more nonstandard formats for it. OK, patch removed from queue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> -----Original Message-----
>
> Is this ready for application? It looks good to me. However, there is
> an "Open issues" section.
In my mind there were two categories of open issues
a) ones that are 100% backward (such as the comment about
outputting this format)
and
b) ones that aren't (such as deprecating the current
postgresql shorthand of
'1Y1M'::interval = 1 year 1 minute
in favor of the ISO-8601
'P1Y1M'::interval = 1 year 1 month.
Attached is a patch that addressed all the discussed issues that
did not break backward compatability, including the ability to
output ISO-8601 compliant intervals by setting datestyle to
iso8601basic.
Ron
Attachment
Peter wrote: > > It would be more useful to implement the SQL standard for intervals first > instead of inventing more nonstandard formats for it. Much of the postgresql docs talks about ISO-8601 formats, so I would think of the patch more as a standards-based improvemnt for the current interval shortand. For example, where today postgresql accepts "1Y1M" as '1 year 1 minute', with the patch, the ISO-8601-standard "P1Y1M" would mean '1 year 1 month'. I would be happy to implement the SQL-standard-intervals as well if someone can point me to that spec. It's just that the system I worked with happend to exchange data with ISO8601 time intervals. Ron Mayer [Moderators of psql-patches... I first replied to Peter from a separate (personal) account. I think this reply is now in the moderation queue). In retrospect I thought it might be better if the archives had the whole thread from the original email address. If it's not too late, could you reject my post in the moderation queue? If not, sorry for the spam.]
Re: ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
From
Randolf Richardson
Date:
[sNip]
> ISO 8601 gives more specific names.
>
> ISO 8601 Basic Format: P2Y10M15DT10H20M30S
> ISO 8601 Alternative Format: P00021015T102030
> ISO 8601 Extended Format: P0002-10-15T10:20:30
>
> In a way, the Extended Format is kinda nice, since it�s
> almost human readable.
>
> I could put in both the basic and extended ones, and
> call the dateformats �iso8601basic� and �iso8601extended�.
> The negative is that to do �iso8601basic� right, I�d also
> have to tweak the �date� and �time� parts of the code too.
Perhaps all three formats should be supported, and if the following
names were all valid things could be simplified further too:
iso8601basic
iso8601bas
iso8601alternative
iso8601alt
iso8601extended
iso8601ext
The reason for allowing shorter names is to simplify database
management for anyone who may need to store the format name in a column for
some reason (I can't think of one now, but I get a feeling that someone
will want to do this type of thing in the future).
For that matter, the first letter could be used instead of the first
three for the short versions. Any thoughts on this?
--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada
Please do not eMail me directly when responding
to my postings in the newsgroups.
Randolf Richardson wrote: > [sNip] > > ISO 8601 gives more specific names. > > > > ISO 8601 Basic Format: P2Y10M15DT10H20M30S > > ISO 8601 Alternative Format: P00021015T102030 > > ISO 8601 Extended Format: P0002-10-15T10:20:30 > > > > In a way, the Extended Format is kinda nice, since it�s > > almost human readable. > > > > I could put in both the basic and extended ones, and > > call the dateformats �iso8601basic� and �iso8601extended�. > > The negative is that to do �iso8601basic� right, I�d also > > have to tweak the �date� and �time� parts of the code too. > > Perhaps all three formats should be supported, and if the following > names were all valid things could be simplified further too: > > iso8601basic > iso8601bas > iso8601alternative > iso8601alt > iso8601extended > iso8601ext > > The reason for allowing shorter names is to simplify database > management for anyone who may need to store the format name in a column for > some reason (I can't think of one now, but I get a feeling that someone > will want to do this type of thing in the future). > > For that matter, the first letter could be used instead of the first > three for the short versions. Any thoughts on this? Just go with the full spellings. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Ron Mayer wrote:
> > -----Original Message-----
> >
> > Is this ready for application? It looks good to me. However, there is
> > an "Open issues" section.
>
> In my mind there were two categories of open issues
> a) ones that are 100% backward (such as the comment about
> outputting this format)
> and
> b) ones that aren't (such as deprecating the current
> postgresql shorthand of
> '1Y1M'::interval = 1 year 1 minute
> in favor of the ISO-8601
> 'P1Y1M'::interval = 1 year 1 month.
>
> Attached is a patch that addressed all the discussed issues that
> did not break backward compatability, including the ability to
> output ISO-8601 compliant intervals by setting datestyle to
> iso8601basic.
>
> Ron
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > Your patch has been added to the PostgreSQL unapplied patches list > at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. I keep reading about open issues, and deprecating certain things, and patch removed, and patch readded. What is going on?
Peter Eisentraut wrote: > Bruce Momjian wrote: > > Your patch has been added to the PostgreSQL unapplied patches list > > at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > I will try to apply it within the next 48 hours. > > I keep reading about open issues, and deprecating certain things, and > patch removed, and patch readded. What is going on? I think the patch just added is OK, no? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > > I keep reading about open issues, and deprecating certain things, > > and patch removed, and patch readded. What is going on? > > I think the patch just added is OK, no? I don't know, but earlier the identical patch was rejected by you.
Peter Eisentraut wrote: > Bruce Momjian wrote: > > > I keep reading about open issues, and deprecating certain things, > > > and patch removed, and patch readded. What is going on? > > > > I think the patch just added is OK, no? > > I don't know, but earlier the identical patch was rejected by you. I thought he made an adjustment so no backward compatibility was broken. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > Peter Eisentraut wrote: > > Bruce Momjian wrote: > > > > I keep reading about open issues, and deprecating certain > > > > things, and patch removed, and patch readded. What is going > > > > on? > > > > > > I think the patch just added is OK, no? > > > > I don't know, but earlier the identical patch was rejected by you. > > I thought he made an adjustment so no backward compatibility was > broken. Then I wouldn't have said "identical".
Peter Eisentraut wrote: > Bruce Momjian wrote: > > Peter Eisentraut wrote: > > > Bruce Momjian wrote: > > > > > I keep reading about open issues, and deprecating certain > > > > > things, and patch removed, and patch readded. What is going > > > > > on? > > > > > > > > I think the patch just added is OK, no? > > > > > > I don't know, but earlier the identical patch was rejected by you. > > > > I thought he made an adjustment so no backward compatibility was > > broken. > > Then I wouldn't have said "identical". OK, can anyone raise an objection to the patch. The new description means to me that he addressed our concerns and that my original hesitation was unwarranted. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Ron Mayer wrote: > > -----Original Message----- > > > > Is this ready for application? It looks good to me. However, there is > > an "Open issues" section. > > In my mind there were two categories of open issues > a) ones that are 100% backward (such as the comment about > outputting this format) > and > b) ones that aren't (such as deprecating the current > postgresql shorthand of > '1Y1M'::interval = 1 year 1 minute > in favor of the ISO-8601 > 'P1Y1M'::interval = 1 year 1 month. > > Attached is a patch that addressed all the discussed issues that > did not break backward compatability, including the ability to > output ISO-8601 compliant intervals by setting datestyle to > iso8601basic. > > Ron [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073