Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
[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 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. 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 - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED]
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 cutpaste 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
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit
And another open issues email. --- Ron Mayer wrote: Tom wrote: Peter Eisentraut [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
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 cutpaste 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] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit deignators
Tom wrote: Ron Mayer [EMAIL PROTECTED] 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: IntervalISO Postgres 8601shorthand - '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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
[EMAIL PROTECTED] 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.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit deignators
Ron Mayer [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit deignators'
Ron Mayer [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 cutpaste 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
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit deignators
Tom wrote: Ron Mayer [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED]
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit
Tom Lane writes:
Ron Mayer [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] ISO 8601 Time Intervals of the format with time-unit deignators
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 cutpaste 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 - 1.123 +++ doc/src/sgml/datatype.sgml 8 Sep 2003 04:04:58 - @@ -1735,20 +1735,71 @@ Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit deignators
Ron Mayer [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
