Re: Bug 1500 - Mailing list pgsql-hackers

From Karel Zak
Subject Re: Bug 1500
Date
Msg-id 1111961655.2388.146.camel@petra
Whole thread Raw
In response to Re: Bug 1500  (Josh Berkus <josh@agliodbs.com>)
Responses to_char(interval) issues
List pgsql-hackers
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 <zakkr@zf.jcu.cz>



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: minor windows & cygwin regression failures on stable branch
Next
From: Andrew Dunstan
Date:
Subject: Re: minor windows & cygwin regression failures on stable