Обсуждение: Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with time-unit designators"
Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with time-unit designators"
От
"Ron Mayer"
Дата:
Tom wrote... > At this point it should move to pghackers, I think. Background for pghackers first, open issues below... Over on pgpatches we've been discussing ISO syntax for time intervals of the format with time-unit designators. http://archives.postgresql.org/pgsql-patches/2003-09/msg00103.php A short summary is that Ive submitted a patch that acceptsintervals of this format.. Postgresql interval: ISO8601 Interval --------------------------------------------------- '1 year 6 months' 'P1Y6M' '3 hours 25 minutes 42seconds' 'PT3H25M42S' The final draft is here ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF This patch was backward-compatable, but further improvements discussed on patches may break compatability so I wantedto discuss them here before implementing them. Ill also be submitting a new datestyle iso8601 to output theseintervals. Open issues: 1. Postgresql supported a shorthand for intervals that had a similar, but not compatable syntax: Interval ISO Existing postgres 8601 shorthand ----------------------------------------------------- '1 year 1 minute' 'P1YT1M' '1Y1M' '1 year 1 month' 'P1Y1M' N/A The current thinking of the thread in pgpatches is to remove the existing (undocumented) syntax. Removing this will break backward compatability if anyone used this feature. Let me know if you needed it. 2. Some of the parsing for intervals is inconsistant and confusing. For example, note that 0.01 years is less than 0.01months. betadb=# select '0.01 month'::interval as hundredth_of_month, betadb-# '0.01 year'::interval as hundredth_of_year; hundredth_of_month | hundredth_of_year --------------------+------------------- 07:12:00 |00:00:00 This occurs because the current interval parsing rounds fractional years to the month, but fractional months to the fractionof a second. The current thinking on the thread in patches is at the very least to make these consistant, but with some open-issuesbecause months arent a fixed number of days, and days arent a fixed number of seconds. The easiest and most minimal change would be to assume that any fractional part automatically gets turned into seconds,assuming things like 30 seconds/month, 24 hrs/day. Since all units except years work that way today, itd wouldhave the least impact on existing code. A probably better way that Tom recommended would remember fractional months and fractional days. This has the advantagethat unlike today, .5 months::interval + .5 months::interval would then equal 1 month. So what should .5 years be? Today, its 6 mons. But I could just as easily argue that it should be 365.2425/2 days, or 4382.91 seconds. Eachof these will be different (the last two are different durring daylight savings). 3. This all is based on the final draft standard of ISO 8601, but I havent seen the actual expensive standard. If anyonehas it handy... Also, Im curious to know what if anything the SQL spec says about intervals and units. Any pointers. Ron Any other interval annoyances I should hit at the same time?
Here is an email on the open issues. --------------------------------------------------------------------------- Ron Mayer wrote: > Tom wrote... > > At this point it should move to pghackers, I think. > > Background for pghackers first, open issues below... > > Over on pgpatches we've been discussing ISO syntax for > ?time intervals? of the ?format with time-unit designators?. > http://archives.postgresql.org/pgsql-patches/2003-09/msg00103.php > A short summary is that I?ve submitted a patch that > accepts intervals of this format.. > Postgresql interval: ISO8601 Interval > --------------------------------------------------- > '1 year 6 months' 'P1Y6M' > '3 hours 25 minutes 42 seconds' 'PT3H25M42S' > The final draft is here > ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF > > This patch was backward-compatable, but further improvements > discussed on patches may break compatability so I wanted to > discuss them here before implementing them. I?ll also > be submitting a new datestyle ?iso8601? to output these intervals. > > Open issues: > > 1. Postgresql supported a shorthand for intervals that had > a similar, but not compatable syntax: > Interval ISO Existing postgres > 8601 shorthand > ----------------------------------------------------- > '1 year 1 minute' 'P1YT1M' '1Y1M' > '1 year 1 month' 'P1Y1M' N/A > > The current thinking of the thread in pgpatches is to remove > the existing (undocumented) syntax. > > Removing this will break backward compatability if anyone > used this feature. Let me know if you needed it. > > 2. Some of the parsing for intervals is inconsistant and > confusing. For example, note that ?0.01 years? is > less than ?0.01 months?. > > betadb=# select '0.01 month'::interval as hundredth_of_month, > betadb-# '0.01 year'::interval as hundredth_of_year; > hundredth_of_month | hundredth_of_year > --------------------+------------------- > 07:12:00 | 00:00:00 > > This occurs because the current interval parsing rounds > fractional years to the month, but fractional months > to the fraction of a second. > > The current thinking on the thread in patches is > at the very least to make these consistant, but with > some open-issues because months aren?t a fixed number > of days, and days aren?t a fixed number of seconds. > > The easiest and most minimal change would be to assume > that any fractional part automatically gets turned > into seconds, assuming things like 30 seconds/month, > 24 hrs/day. Since all units except years work that way > today, it?d would have the least impact on existing code. > > A probably better way that Tom recommended would remember > fractional months and fractional days. This has the > advantage that unlike today, > ?.5 months?::interval + ?.5 months?::interval > would then equal 1 month. > > So what should ?.5 years? be? > > Today, it?s ?6 mons?. But I could just as easily > argue that it should be 365.2425/2 days, or 4382.91 > seconds. Each of these will be different (the last > two are different durring daylight savings). > > 3. This all is based on the final draft standard of > ISO 8601, but I haven?t seen the actual expensive > standard. If anyone has it handy... > > Also, I?m curious to know what if anything the SQL > spec says about intervals and units. Any pointers. > > Ron > > Any other interval annoyances I should hit at the same time? > > > ---------------------------(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, Pennsylvania19073