On Sun, 2005-03-27 at 11:43 -0800, Josh Berkus wrote: > Tom, Karel, > > > Hmm, if we want to support conversion like: > > '43 hours 20 minutes' --> 'MI min' > > how we should work with calendar INTERVAL units? For example 'month'? > > '1 month 1 day' --> 'D days' > > I think answer should be error message: "missing calendar unit 'month' > > in output format" > > Actually, there's a pretty well-defined boundary within interval types: > year.month | day.hour.minute.second.millesecond
Yes. > This subtype boundary of intervals is even defined in the SQL spec. > > > Surely not. to_char for timestamps doesn't require that you output > > every field of the value, and it shouldn't require that for intervals > > either. > > That's an invalid comparison. There is no logical way to "roll up" > timestamps > into larger/smaller subtypes. There is with intervals. Agree. There is two possible way how you can convert it: a) extract and convert '1h 10min 30s' --- 'MI "min"' ---> '10 min' b) hold the interval and convert it to defined units '1h 10min 30s' --- 'MI "min"' ---> '70.5 min' > If you're arguing that this kink in the *useful* behavior of interval-->text > conversion is confusingly inconsistent with what to_char does with other data > types, and we should call the function something else, then I could > potentially buy that (assuming that others agree). However, our proprietary > functions are about being *useful*, not adhering to some unwritten de-facto > standard. And I am, as someone who uses intervals heavily in applications, > trying to define what the useful behaviour will be from a user's perspective. I agree with Josh that for interval is more useful second way where result from conversion is still useful interval. There is no problem implement both, to_char() stuff already supports global options and I can add for INTERVAL option 'EX' as extract. a) to_char('1h 10min 30s', 'EXMI "min"') -> '10 min' b) to_char('1h 10min 30s', 'MI "min"') -> '70.5 min' BTW, for numbers to_char() disable extraction: test=# select to_char(123.4::float, '.999'); to_char --------- .### the result is not '.4'. I think important is always tradition how people work with selected datetype. For TIMESTAMP is it common that you work with extraction from full date/time description, but it's unusual for numbers and I think for INTERVALs too. Karel -- Karel Zak <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings