Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with time-unit designators"
От | Ron Mayer |
---|---|
Тема | Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with time-unit designators" |
Дата | |
Msg-id | POEDIPIPKGJJLDNIEMBEKEBDDJAA.ron@intervideo.com обсуждение исходный текст |
Ответы |
Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with
(Bruce Momjian <pgman@candle.pha.pa.us>)
|
Список | pgsql-hackers |
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?
В списке pgsql-hackers по дате отправления: