to_char(interval) issues - Mailing list pgsql-hackers

Based on this discussion I have added these TODO items:

* Prevent to_char() on interval from returning meaningless values
 For example, to_char('1 month', 'mon') is meaningless.  Basically, most date-related parameters to to_char() are
meaninglessfor intervals because interval is not anchored to a date.
 

* Allow to_char() on interval values to accumulate the highest unit requested
       o to_char(INTERVAL '1 hour 5 minutes', 'MI') => 65       o to_char(INTERVAL '43 hours 20 minutes', 'MI' ) =>
2600      o to_char(INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI') => 0:1:19:20       o to_char(INTERVAL '3 years 5
months','MM')=> 41
 
 Some special format flag would be required to request such accumulation.  Such functionality could also be added to
EXTRACT.Prevent accumulation that crosses the month/day boundary because of the uneven number of days in a month.
 

---------------------------------------------------------------------------

Karel Zak wrote:
> 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>
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

--  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