Re: to_char(interval) --- done?

Guys,

> I volunteered to look into it, but got a sorta negative reply from Peter_E,
> but no
> response to my request for suggestions.

Glad I asked then. I've had trouble keeping up with HACKERS lately; too much
traffic!

While I can't write the code, I can certainly make a proposal:

Y = years, fixed digits: YYYY = '0019' for ninteen years. Only whole years
will be listed.
y = years, optional digits: yyyy = '19' for nineteen years.
M = months, fixed digits: 'MMMM' = '0019' for nineteen months. If months are
displayed, but not years, all months will be shown, i.e. '45 months'. If
years are displayed as well, only the remainder of months will be displayed,
i.e. '3 years 9 months'.
m = months, optional digits. Otherwise, same as above.
D = days, fixed digits. only whole days are displayed.
d = days, optional digits.
H = hours, fixed digits. If hours are displyaed but not days, hours will show
all hours, e.g. '32 hours'. If days are displayed, only the remainder, e.g.
'1 day 6 hours'.
h = hours, optional digits.
I = Minutes, fixed digits. If minutes are displayed but not hours, mintues
will be total minutes, e.g. : '135 minutes'. If hours are displayed as well,
then it will be only the remainder, e.g. '2 hours 15 minutes'
i = minutes, optional digits. Same as above.
S = seconds, fixed digits. If seconds are displayed but not minutes, then
all seconds will be shown, e.g. '260 seconds'. If minutes are displayed,
seconds will be only the remainder, e.g. '4 minutes 20 seconds'.
s = seconds, optional digits.
C, c = milliseconds, optional or fixed. Same rules on interaction with higher
hours/minutes/seconds intervals as above.

Any of the above you want to use as regular characters would be escaped,
either with \ or by enclosing in quotes. This is *not* consistent with
current to_char functioning, but I can't see any way around it, given the
number of different lables users might want. For example:

to_char(some_interval, 'hhh\h ii\m ss\s CCC\m\s') = '37h 21m 23s 012ms'
to_char(some_interval, 'yyy "Years" mm "Months"') = '4 Years 6 Months'

Obviously, this proposal needs some work, particularly to make it more
consistent with the functioning of other to_char functions, but It fulfills
the rough requirements of:

a) allowing users to display any interval as a meaningful text string.
b) maintaining the SQL-spec 2-subtype break of Years/Months and
Days/Hours/Minutes/Seconds/Milleseconds.

(P.S. please include me cc: in your comments; I'm on digest and otherwise may
take a day or more to reply)

--
Josh Berkus
Aglio Database Solutions
San Francisco