Horton

Thread

  1. Re: Bug 1500

    lsunley@mb.sympatico.ca — 2005-03-26T21:49:50Z

    This has my vote....

Lorne

In <200503261404.14979.josh@agliodbs.com>, on 03/26/05 
   at 02:04 PM, Josh Berkus <josh@agliodbs.com> said:

>Karel,

>> > Yeah.  áKarel Zak, who wrote that code, is convinced we should remove it,
>> > but I don't think anyone else is ...
>>
>>  áI think I was Peter and Josh Berkus who convinced me that the code is
>> bed. "we should remove..." is opinion only...

>I certainly didn't recommend removing it before we have a replacement
>ready.  

>The complaint, btw, was that the current to_char formats intervals as if
>they  were dates.   This results in some rather confusing output.   I
>wanted to  improve to_char to support proper interval formatting, but
>apparently it's  difficult to do that without breaking other aspects of
>to_char (at least, I  was told that).   

>What we need is a function or functions which do the following:

>SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min'; 2600
>min

>SELECT to_char( INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI' );
>0:1:19:20

>SELECT to_char( INTERVAL '3 years 5 months','MM' ) || ' mons'; 41 mons

>etc.  This would be more sophisticated than the logic employed for the
>current  to_char, as the interval would be re-calculated in the units
>supplied,  limited by the month/year|day/hour/minute boundary.   



-- 
-----------------------------------------------------------
lsunley@mb.sympatico.ca
-----------------------------------------------------------

  2. Re: Bug 1500

    Josh Berkus <josh@agliodbs.com> — 2005-03-26T22:04:14Z

    Karel,

> > Yeah.  Karel Zak, who wrote that code, is convinced we should remove it,
> > but I don't think anyone else is ...
>
>  I think I was Peter and Josh Berkus who convinced me that the code is
> bed. "we should remove..." is opinion only...

I certainly didn't recommend removing it before we have a replacement ready.  

The complaint, btw, was that the current to_char formats intervals as if they 
were dates.   This results in some rather confusing output.   I wanted to 
improve to_char to support proper interval formatting, but apparently it's 
difficult to do that without breaking other aspects of to_char (at least, I 
was told that).   

What we need is a function or functions which do the following:

SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min';
2600 min

SELECT to_char( INTERVAL '43 hours 20 minutes', 'WK:DD:HR:MI' );
0:1:19:20

SELECT to_char( INTERVAL '3 years 5 months','MM' ) || ' mons';
41 mons

etc.  This would be more sophisticated than the logic employed for the current 
to_char, as the interval would be re-calculated in the units supplied, 
limited by the month/year|day/hour/minute boundary.   

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

  3. Re: Bug 1500

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2005-03-26T23:13:02Z

    On Sat, Mar 26, 2005 at 02:04:14PM -0800, Josh Berkus wrote:

> SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min';
> 2600 min

Hmm, what if you wanted more than one literal string?  Say "1 mon 3
days" ... your concatenation idea wouldn't work.  ISTM the format string
should allow unconverted literals, so you would use

SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' );

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Cuando no hay humildad las personas se degradan" (A. Christie)

  4. Re: Bug 1500

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-03-26T23:39:42Z

    Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> ...  ISTM the format string
> should allow unconverted literals, so you would use

> SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' );

... which to_char can do already, IIRC.  The rewrite should define a new
set of format substitution codes, but not otherwise change the behavior
of to_char.

			regards, tom lane

  5. Re: Bug 1500

    Josh Berkus <josh@agliodbs.com> — 2005-03-26T23:56:43Z

    Alvaro,

> On Sat, Mar 26, 2005 at 02:04:14PM -0800, Josh Berkus wrote:
> > SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min';
> > 2600 min
>
> Hmm, what if you wanted more than one literal string?  Say "1 mon 3
> days" ... your concatenation idea wouldn't work.  ISTM the format string
> should allow unconverted literals, so you would use
>
> SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' );

Hmmm, good point.

Question: how does to_char tell the difference between a code ("MI") and a 
code which is also part of a word? ("MIN").

--Josh

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

  6. Re: Bug 1500

    Karel Zak <zakkr@zf.jcu.cz> — 2005-03-27T10:03:52Z

    On Sat, 2005-03-26 at 15:56 -0800, Josh Berkus wrote:
> Alvaro,
> 
> > On Sat, Mar 26, 2005 at 02:04:14PM -0800, Josh Berkus wrote:
> > > SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min';
> > > 2600 min
> >
> > Hmm, what if you wanted more than one literal string?  Say "1 mon 3
> > days" ... your concatenation idea wouldn't work.  ISTM the format string
> > should allow unconverted literals, so you would use
> >
> > SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' );
> 
> Hmmm, good point.
> 
> Question: how does to_char tell the difference between a code ("MI") and a 
> code which is also part of a word? ("MIN").

It's pretty simple. to_char(..., 'MI "min"'). It's already supported by
to_char() format parser.

I think to_char(interval) should be support split interval to more
items, like:

to_char(INTERVAL '1d 3h 65s', 'HHh MIm SSs')  --->  '27h 1m 5s'

Well, I'm going to check how difficult will be implement correct to_char
(interval).

	Karel

-- 
Karel Zak <zakkr@zf.jcu.cz>

  7. Re: Bug 1500

    Karel Zak <zakkr@zf.jcu.cz> — 2005-03-27T11:03:33Z

    On Sun, 2005-03-27 at 12:03 +0200, Karel Zak wrote:
> On Sat, 2005-03-26 at 15:56 -0800, Josh Berkus wrote:
> > Alvaro,
> > 
> > > On Sat, Mar 26, 2005 at 02:04:14PM -0800, Josh Berkus wrote:
> > > > SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min';
> > > > 2600 min
> > >
> > > Hmm, what if you wanted more than one literal string?  Say "1 mon 3
> > > days" ... your concatenation idea wouldn't work.  ISTM the format string
> > > should allow unconverted literals, so you would use
> > >
> > > SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' );
>
> Well, I'm going to check how difficult will be implement correct to_char
> (interval).

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"

	Karel
-- 
Karel Zak <zakkr@zf.jcu.cz>

  8. Re: Bug 1500

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-03-27T15:58:28Z

    Karel Zak <zakkr@zf.jcu.cz> writes:
> 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"

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.

regression=# select to_char(now(), 'MI "min"');
 to_char
---------
 58 min
(1 row)

			regards, tom lane

  9. Re: Bug 1500

    Josh Berkus <josh@agliodbs.com> — 2005-03-27T19:43:53Z

    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

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.

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.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

  10. Re: Bug 1500

    Karel Zak <zakkr@zf.jcu.cz> — 2005-03-27T22:14:15Z

    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>

  11. to_char(interval) issues

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-05-07T04:16:02Z

    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 meaningless for
  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, Pennsylvania 19073

  12. Re: to_char(interval) issues

    Josh Berkus <josh@agliodbs.com> — 2005-05-07T17:58:00Z

    Bruce,

> * Prevent to_char() on interval from returning meaningless values

> * Allow to_char() on interval values to accumulate the highest unit
>   requested

Sounds like it would cover my use cases.   Others?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco