Re: Re: Interval bug
От | Bruce Momjian |
---|---|
Тема | Re: Re: Interval bug |
Дата | |
Msg-id | 200101112124.QAA07355@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: Interval bug (Thomas Lockhart <lockhart@alumni.caltech.edu>) |
Список | pgsql-bugs |
Wow, this was interesting. I never suspected that in: -1 year 360 days 00:00 the "360 days" is positive, while the "-1 year" was negative, but I can now see how that can very easily happen. Should we print the "days" +/- sign only when it is not the same as the "year" sign? Seems like the way to go. > > Secondly, we have a problem with interval math, detailed below. Should > > be reproducible on any platform(?) > > Version: 7.0.2 (is it fixed in the latest?) > > Yes, but may need a little more fixup... > > > =# select '2 years ago'::interval + '1 year 360 days 00:00'::interval; > > ?column? > > ---------------------- > > 1 year 360 00:00 ago > > Wrong! (Should be 5 or 6 days depending on interpretation?) > > I'd forgotten about this until your posting. Internally, the math is > being done correctly. But, the output representation for earlier > releases does not handle "mixed signs" at all well. In particular, > months and years are stored in one field (saved as months), and days, > hours, etc are stored in another (saved as seconds), so if there is a > sign flip between the two fields it needs to be explicitly mentioned in > the output. > > In your test case, the result has a sign flip between the months and > seconds. So, the result above *should* be something like > > -1 year +360 days > > The "ago" representation just adds to the confusion: "-360 days ago" > seems too easy to misread or misinterpret to be useful. > > 7.1 will represent this as > > -1 year +360 days 00:00 > > and the 7.1beta tarball represents this as > > -1 year 360 +00:00 > > (note lack of explicit plus sign in front of the days field, which means > that it still isn't quite right for least ambiguity). > > I've done some initial testing on patches which result in signs on all > fields once a negative value is seen, and this seems to be the least > troublesome solution. The patches also put an explicit "day(s)" on the > days field, if any. > > Fixes will appear in the next beta (beta4?) unless there are objections. > Let me know if you need the patches. > > Thanks for the report! > > - Thomas > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
В списке pgsql-bugs по дате отправления: