Hi,

I have found what is causing the crash described in Bug 1500. Now I
would like to fix it, but need opinions about what is the correct behaviour.

The bug can be easily duplicated when formatting interval in to_char()
using 'Mon' or 'Month' in the format string.

select to_char(now() - '20011001'::date, 'YYYYMonDD');
(server process crash follows)


What happens:
1. The formatting function used is dch_date()
(src/backend/utils/adt/formatting.c) and it works on struct pg_tm.
2. The interval2tm() (src/backend/utils/adt/timestamp.c) is used to
convert the interval into pg_tm struct.
2a. If the Interval parameter has month != 0, then month and year are
filled in pg_tm
2b. If not -> they are set to 0 and only days, hours, minutes, seconds
are filled (this is the case when the bug appears).
3. dch_date() expects the struct pg_tm to have valid 1-based month index
and directly references the months/months_full arrays using (tm->month -
1) as index to get the short/full name of the month.
4. SIGSEGV in the server process

This could be easily by not allowing the bad array indexing, but it
raises a bigger problem: How is supposed the to_char() function to
format interval datatype? What is the correct output?

Should we:
1) Try to fill the missing data (years, months) using the days (but how
many days are in one month? hardcode 30/31? how many days in 1 year
then...) and fix the formatting function to ignore string based
formatting for intervals
2) Fail the entire statement (do not support interval formatting with
to_char())


Also the general to_char() Interval formatting seems broken anyway.
Note that the following (and similar) works now, but the result doesn't
seem to be correct:

test=> select to_char(now() - '20011001'::date, 'YYYYDD');
to_char
---------
000112
(1 row)

test=> select now() - '20011001'::date;
?column?
-------------------------------
1271 days 12:48:18.1216260046
(1 row)


So this bug actually brings the issue of interval to_char() formatting.
Opinions?


Regards,
Lyubomir Petrov

Search Discussions

  • Tom Lane at Mar 25, 2005 at 8:33 pm

    Lyubomir Petrov writes:
    I have found what is causing the crash described in Bug 1500. Now I
    would like to fix it, but need opinions about what is the correct behaviour.
    Yeah, I just came to the same conclusion a little while ago:
    http://archives.postgresql.org/pgsql-hackers/2005-03/msg00908.php
    Also the general to_char() Interval formatting seems broken anyway.
    Karel Zak has stated repeatedly that interval_to_char is fundamentally
    wrong and should be removed. I'm not sure it's quite as bad as that,
    but it does seem that a different set of formatting codes is needed for
    intervals as opposed to timestamps. Textual 'MON' doesn't even make any
    sense for intervals really, AFAICS. I could see displaying an interval
    in terms of '4 months', but 'April' makes no sense.

    Does Oracle have to_char for intervals, and if so how do they define it?

    Anyway, even if we think it's broken enough to remove going forward,
    we need some kind of stopgap fix to prevent the coredump in existing
    releases.

    regards, tom lane
  • Lyubomir Petrov at Mar 25, 2005 at 9:28 pm

    Tom Lane wrote:
    Lyubomir Petrov <lpetrov@sysmaster.com> writes:

    I have found what is causing the crash described in Bug 1500. Now I
    would like to fix it, but need opinions about what is the correct behaviour.
    Yeah, I just came to the same conclusion a little while ago:
    http://archives.postgresql.org/pgsql-hackers/2005-03/msg00908.php


    Also the general to_char() Interval formatting seems broken anyway.
    Karel Zak has stated repeatedly that interval_to_char is fundamentally
    wrong and should be removed. I'm not sure it's quite as bad as that,
    but it does seem that a different set of formatting codes is needed for
    intervals as opposed to timestamps. Textual 'MON' doesn't even make any
    sense for intervals really, AFAICS. I could see displaying an interval
    in terms of '4 months', but 'April' makes no sense.

    Does Oracle have to_char for intervals, and if so how do they define it?

    Anyway, even if we think it's broken enough to remove going forward,
    we need some kind of stopgap fix to prevent the coredump in existing
    releases.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    http://archives.postgresql.org

    .

    Tom,

    Well, I can see how the to_char functionality can be very useful for
    intervals - one can get the interval in days only, months and days, etc.
    But I think that the format specifications that convert to strings
    should be disallowed for intervals (Mon, Month, etc...).

    If we decide just to ignore the non-supported format code we can
    1) make dch_date aware that it is called for interval and limit the
    choices (ignore the attempt to show textual name representation for
    example)
    2) just ignore the attempt to show month name on invalid value in struct
    pg_tm.

    In the second case we'll need to change only this file several times
    using something like (this is good to be there anyway because of the
    array indexing):

    case DCH_Mon:
    + if (tm->tm_mon > 0) {
    + strcpy(inout, months[tm->tm_mon - 1]);
    + return 2;
    + }
    + return -1;
    +
    - strcpy(inout, months[tm->tm_mon - 1]);
    - return 2;

    The first case will probably have more impact. I think we can go with 2)
    for 8.0.2 and 1) for 8.1.

    Oracle has to_char() on intervals, but generally does not allow fancy
    formatting (limited format specifications only - FF, TZD, TZH, TZM, and
    TZR - which are not very useful anyway).


    Regards,
    Lyubomir Petrov
  • Karel Zak at Mar 26, 2005 at 1:15 am

    On Fri, 2005-03-25 at 15:33 -0500, Tom Lane wrote:
    Lyubomir Petrov <lpetrov@sysmaster.com> writes:
    I have found what is causing the crash described in Bug 1500. Now I
    would like to fix it, but need opinions about what is the correct behaviour.
    Yeah, I just came to the same conclusion a little while ago:
    http://archives.postgresql.org/pgsql-hackers/2005-03/msg00908.php
    Also the general to_char() Interval formatting seems broken anyway.
    Karel Zak has stated repeatedly that interval_to_char is fundamentally
    wrong and should be removed. I'm not sure it's quite as bad as that,
    but it does seem that a different set of formatting codes is needed for
    intervals as opposed to timestamps.
    Exactly. We had many discussions about it. Well, short summary:

    the current to_char(interval) is:

    interval -> struct tm -> string

    and it's definitely bad. You can't formatting interval as date/time
    string and you can't use calendar practices in particular case.

    The right solution is conversion:

    interval -> interval-string

    and it means definitely other (new) code for to_char(interval). I think
    useful for to_char(interval) is only format parser from formatting.c,
    it's 5% of all to_char() code :-(

    I don't think we want to maintain useless code in PG and answer every
    month in PG lists questions "why doesn't work it?". It's better remove
    it and wait for someone who write better implementation.

    BTW, I have started work on formatting library:

    http://people.redhat.com/kzak/libfmt/

    contributors, volunteers? :-)

    Karel

    --
    Karel Zak <zakkr@zf.jcu.cz>
  • Steve Crawford at Mar 25, 2005 at 8:53 pm

    So this bug actually brings the issue of interval to_char()
    formatting. Opinions?
    In digging around I discovered that it appears a decision was made to
    remove to_char(interval) at the 8.1 release but I've been unable to
    find the replacement for this functionality. This alarms me.

    Given the messages I've seen regarding to_char(interval), it's clearly
    a function that is used. As an example, in our telephony systems
    there is a column for start_time and for end_time. Billing involves a
    sum(end_time-start_time) for the appropriate project/client/period.
    Naturally, that interval needs to be displayed appropriately.

    The most common request I've seen (and it would be very helpful for me
    as well) is the ability to fill the largest displayed time increment
    with all remaining time in the interval.

    In other words when the total increment is 7 days, 7 hours, 28
    minutes, 12 seconds the desired output would be 10528 minutes 12
    seconds. Think phone-billing, race times, mission clocks, etc.

    So...

    1) Is there really a plan to eliminate to_char(interval)?

    2) If so, what is the replacement?

    3) If there isn't a replacement and it's just scheduled for
    elimination, what harm was to_char(interval) causing to require its
    removal and what's the best way to lobby for its retention and
    improvement?

    Cheers,
    Steve
  • Lyubomir Petrov at Mar 25, 2005 at 9:22 pm

    Steve Crawford wrote:

    So this bug actually brings the issue of interval to_char()
    formatting. Opinions?
    In digging around I discovered that it appears a decision was made to
    remove to_char(interval) at the 8.1 release but I've been unable to
    find the replacement for this functionality. This alarms me.

    Given the messages I've seen regarding to_char(interval), it's clearly
    a function that is used. As an example, in our telephony systems
    there is a column for start_time and for end_time. Billing involves a
    sum(end_time-start_time) for the appropriate project/client/period.
    Naturally, that interval needs to be displayed appropriately.

    The most common request I've seen (and it would be very helpful for me
    as well) is the ability to fill the largest displayed time increment
    with all remaining time in the interval.

    In other words when the total increment is 7 days, 7 hours, 28
    minutes, 12 seconds the desired output would be 10528 minutes 12
    seconds. Think phone-billing, race times, mission clocks, etc.

    So...

    1) Is there really a plan to eliminate to_char(interval)?

    2) If so, what is the replacement?

    3) If there isn't a replacement and it's just scheduled for
    elimination, what harm was to_char(interval) causing to require its
    removal and what's the best way to lobby for its retention and
    improvement?

    Cheers,
    Steve

    .

    Steve,

    I am with you on this. The interval functionality is very useful and it
    will be bad if it gets eliminated. I believe that the best course of
    action is to keep the to_char(interval) but restrict the available
    format specifications (the textual representation specificators like
    Mon/Months).

    Regards,
    Lyubomir Petrov
  • Tom Lane at Mar 26, 2005 at 1:03 am

    Steve Crawford writes:
    In digging around I discovered that it appears a decision was made to
    remove to_char(interval) at the 8.1 release but I've been unable to
    find the replacement for this functionality. This alarms me.
    Yeah. Karel Zak, who wrote that code, is convinced we should remove it,
    but I don't think anyone else is ...

    regards, tom lane
  • Karel Zak at Mar 26, 2005 at 1:29 am

    On Fri, 2005-03-25 at 20:03 -0500, Tom Lane wrote:
    Steve Crawford <scrawford@pinpointresearch.com> writes:
    In digging around I discovered that it appears a decision was made to
    remove to_char(interval) at the 8.1 release but I've been unable to
    find the replacement for this functionality. This alarms me.
    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...

    http://groups-
    beta.google.com/group/comp.databases.postgresql.hackers/browse_frm/thread/a43f02de8017cabb/c290bc55d5e1e6b2?q=to_char(interval)+done&rnum=1#c290bc55d5e1e6b2



    --
    Karel Zak <zakkr@zf.jcu.cz>
  • Karel Zak at Mar 26, 2005 at 1:50 am

    On Sat, 2005-03-26 at 02:32 +0100, Karel Zak wrote:
    On Fri, 2005-03-25 at 20:03 -0500, Tom Lane wrote:
    Steve Crawford <scrawford@pinpointresearch.com> writes:
    In digging around I discovered that it appears a decision was made to
    remove to_char(interval) at the 8.1 release but I've been unable to
    find the replacement for this functionality. This alarms me.
    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...
    s/bed/bad/ :-)

    .. but my body dreams about bed, good night (morning?),

    Karel

    --
    Karel Zak <zakkr@zf.jcu.cz>
  • Bruno Wolff III at Mar 26, 2005 at 3:14 am

    On Fri, Mar 25, 2005 at 12:53:53 -0800, Steve Crawford wrote:

    2) If so, what is the replacement?
    You should be able to use EXTRACT, some math to do your own formatting.
    For common operations you can define SQL functions to do what you want.
    Having to_char(interval) may be more convenient (if it does what you
    want), but you can get by without it.
  • Josh Berkus at Mar 26, 2005 at 10:04 pm
    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
  • Lsunley at Mar 26, 2005 at 10:52 pm
    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
    -----------------------------------------------------------
  • Alvaro Herrera at Mar 26, 2005 at 11:13 pm

    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)
  • Tom Lane at Mar 26, 2005 at 11:39 pm

    Alvaro Herrera 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
  • Josh Berkus at Mar 26, 2005 at 11:52 pm
    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
  • Karel Zak at Mar 27, 2005 at 10:00 am

    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>
  • Karel Zak at Mar 27, 2005 at 11:00 am

    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>
  • Tom Lane at Mar 27, 2005 at 3:58 pm

    Karel Zak 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
  • Josh Berkus at Mar 27, 2005 at 7:44 pm
    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
  • Karel Zak at Mar 27, 2005 at 10:10 pm

    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>
  • Bruce Momjian at May 7, 2005 at 4:16 am
    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
  • Josh Berkus at May 7, 2005 at 5:58 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 25, '05 at 7:55p
activeMay 7, '05 at 5:58p
posts22
users9
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase