to_char(interval) --- done?

On Fri, Mar 21, 2003 at 03:32:11PM -0800, Josh Berkus wrote:

Folks,

Some months ago we agreed on this list that the functioning of
to_char(interval) was not particularly useful the way it works in current
stable versions. I don't see a to_char fix on the TODO list, though; does
that mean it's already been fixed in 7.4?

No. There was short discussion about it last week.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

--On Monday, March 24, 2003 09:40:46 +0100 Karel Zak <zakkr@zf.jcu.cz>
wrote:

On Fri, Mar 21, 2003 at 03:32:11PM -0800, Josh Berkus wrote:

Folks,

Some months ago we agreed on this list that the functioning of
to_char(interval) was not particularly useful the way it works in
current stable versions. I don't see a to_char fix on the TODO list,
though; does that mean it's already been fixed in 7.4?

No. There was short discussion about it last week.

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

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Mon, Mar 24, 2003 at 04:34:38AM -0600, Larry Rosenman wrote:

--On Monday, March 24, 2003 09:40:46 +0100 Karel Zak <zakkr@zf.jcu.cz>
wrote:

On Fri, Mar 21, 2003 at 03:32:11PM -0800, Josh Berkus wrote:

Folks,

Some months ago we agreed on this list that the functioning of
to_char(interval) was not particularly useful the way it works in
current stable versions. I don't see a to_char fix on the TODO list,
though; does that mean it's already been fixed in 7.4?

No. There was short discussion about it last week.

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

I don't check where is a problem in detail, but I will fix something
in to_char() code now and I will try check interval problem too. I
think the basic problem is in interval2tm() function -- maybe we will
need some other method how convert interval to 'tm' struct (the
to_char() code is based on 'tm').

I'm open for all suggestions and volunteers of course.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

On Mon, Mar 24, 2003 at 04:34:38AM -0600, Larry Rosenman wrote:

--On Monday, March 24, 2003 09:40:46 +0100 Karel Zak <zakkr@zf.jcu.cz>
wrote:

On Fri, Mar 21, 2003 at 03:32:11PM -0800, Josh Berkus wrote:

Folks,

Some months ago we agreed on this list that the functioning of
to_char(interval) was not particularly useful the way it works in
current stable versions. I don't see a to_char fix on the TODO list,
though; does that mean it's already been fixed in 7.4?

No. There was short discussion about it last week.

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

I think what Peter was saying is to research some ways to manage
intervals and other time related data types within what the SQL standard
defines. to_char() and the like are only Oracle compatibility functions
and should not be taken as serious ways to do things.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)

On Mon, Mar 24, 2003 at 09:56:53AM -0400, Alvaro Herrera wrote:

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

I think what Peter was saying is to research some ways to manage
intervals and other time related data types within what the SQL standard
defines. to_char() and the like are only Oracle compatibility functions
and should not be taken as serious ways to do things.

The to_char() knows formatting numbers, time/date to almost arbitrary
string. I unsure if SQL standard knows something like this and write
something for "interval" only is not good idea (IMHO) if there is a lot of
code which already know formatting data to string.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

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

Karel,

If I see this I must agree with Peter that to_char() is something
other than "interval" to "interval-as-string" convertor. The current
code use date/time as complex of date/time information _based_ on
calendar practice --
The other words: current to_char(interval) is interval to calendar
date/time convertor.

Currently, yes. The reason why I'm advocating for a change is:

1) I can't imagine of what use the current behavior could possibly be. Is
there anyone at all using the current output of to_char(interval)?

2) to_char() is also used for converting numeric values to strings. It is
not in some way tied to date/time from a schema perspective, although it may
be codewise.

I think we can do with the current to_char(interval):

a) maintain it as "interval" to "calendar date/time string" convertor,
b) if nobody wants to use it as a) we can delete it from sources
and don't waste our time with it and use our time to real
"interval" convertor.

This sounds reasonable to me. I'll even do a survey on the SQL list to see if
anyone there is using the current behavior.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Tue, Mar 25, 2003 at 09:28:09AM -0800, Josh Berkus wrote:

Karel,

If I see this I must agree with Peter that to_char() is something
other than "interval" to "interval-as-string" convertor. The current
code use date/time as complex of date/time information _based_ on
calendar practice --
The other words: current to_char(interval) is interval to calendar
date/time convertor.

Currently, yes. The reason why I'm advocating for a change is:

1) I can't imagine of what use the current behavior could possibly be. Is
there anyone at all using the current output of to_char(interval)?

IMHO nobody use it -- maybe we can keep it in sources for 7.4 and
mark it in docs as deprecated and remove it in 7.5.

2) to_char() is also used for converting numeric values to strings. It is
not in some way tied to date/time from a schema perspective, although it may
be codewise.

The date/time and numbers formatting share parser only. The current
to_char(interval) is 20 lines of code only.

I think we can do with the current to_char(interval):

a) maintain it as "interval" to "calendar date/time string" convertor,
b) if nobody wants to use it as a) we can delete it from sources
and don't waste our time with it and use our time to real
"interval" convertor.

This sounds reasonable to me. I'll even do a survey on the SQL list to see if
anyone there is using the current behavior.

I want to write new library "libformattig", because I need to_char()
features and some new extensions in the others projects -- it means
in 7.5 will new to_char() code. The current code works without bugs,
but it is not ideal code.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Karel,

IMHO nobody use it -- maybe we can keep it in sources for 7.4 and
mark it in docs as deprecated and remove it in 7.5.

This seems to be consistent with my informal survey on PGSQL-SQL and on
#postgresql. Nobody seems to be using the current behavior.

The date/time and numbers formatting share parser only. The current
to_char(interval) is 20 lines of code only.

I'm not surprised.

I want to write new library "libformattig", because I need to_char()
features and some new extensions in the others projects -- it means
in 7.5 will new to_char() code. The current code works without bugs,
but it is not ideal code.

Please call on me if you want any additional help formulating a specification.
While I am not in any position to help with the code, I do use INTERVAL,
TIMESTAMP, TIME and DATE heavily and have strong opinions about usability.

Thanks!

--
Josh Berkus
Aglio Database Solutions
San Francisco

--On Wednesday, March 26, 2003 08:38:58 -0800 Josh Berkus
<josh@agliodbs.com> wrote:

Karel,

IMHO nobody use it -- maybe we can keep it in sources for 7.4 and
mark it in docs as deprecated and remove it in 7.5.

This seems to be consistent with my informal survey on PGSQL-SQL and on
# postgresql. Nobody seems to be using the current behavior.

The date/time and numbers formatting share parser only. The current
to_char(interval) is 20 lines of code only.

I'm not surprised.

I want to write new library "libformattig", because I need to_char()
features and some new extensions in the others projects -- it means
in 7.5 will new to_char() code. The current code works without bugs,
but it is not ideal code.

Please call on me if you want any additional help formulating a
specification. While I am not in any position to help with the code, I
do use INTERVAL, TIMESTAMP, TIME and DATE heavily and have strong
opinions about usability.

I had a need that should(!) be in the archives. Just to reiterate my need:

I input a contract length, in months, and wanted to get it back out as
months. There is no current way to do this, so I store it as an INT and
concatenate the word months to do the
date arithmetic.

Thanks all for the input, I suspect I will leave the coding to someone like
Karel that knows the backend better than I do, and also is a better coder
than I am.

Thanks!

--
Josh Berkus
Aglio Database Solutions
San Francisco

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

http://archives.postgresql.org

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry,

I had a need that should(!) be in the archives. Just to reiterate my need:

I input a contract length, in months, and wanted to get it back out as
months. There is no current way to do this, so I store it as an INT and
concatenate the word months to do the
date arithmetic.

What about EXTRACT(months FROM <interval_field>)?

This annoyingly wraps around if it's more than 12 months, but that was part of
my proposal ...

--
Josh Berkus
Aglio Database Solutions
San Francisco

--On Wednesday, March 26, 2003 08:50:36 -0800 Josh Berkus
<josh@agliodbs.com> wrote:

Larry,

I had a need that should(!) be in the archives. Just to reiterate my
need:

I input a contract length, in months, and wanted to get it back out as
months. There is no current way to do this, so I store it as an INT and
concatenate the word months to do the
date arithmetic.

What about EXTRACT(months FROM <interval_field>)?

This annoyingly wraps around if it's more than 12 months, but that was
part of my proposal ...

I needed like 5 years (60 months)...

So, the wrap around is not good in this case.

Thanks, though.

--
Josh Berkus
Aglio Database Solutions
San Francisco

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry,

What about EXTRACT(months FROM <interval_field>)?

This annoyingly wraps around if it's more than 12 months, but that was
part of my proposal ...

I needed like 5 years (60 months)...

So, the wrap around is not good in this case.

Easy:
CREATE FUNCTION show_months ( interval ) returns text as '
SELECT CAST( (( extract(years from $1) * 12 ) + extract(months from $1)) AS
text ) || '' months'';
' LANGUAGE SQL IMMUTABLE STRICT;

(above not checked for typos)

I'd swear that you posted your question on pgsql-sql and I posted the above
solution some months ago.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

--On Wednesday, March 26, 2003 09:53:58 -0800 Josh Berkus
<josh@agliodbs.com> wrote:

Larry,

What about EXTRACT(months FROM <interval_field>)?

This annoyingly wraps around if it's more than 12 months, but that was
part of my proposal ...

I needed like 5 years (60 months)...

So, the wrap around is not good in this case.

Easy:
CREATE FUNCTION show_months ( interval ) returns text as '
SELECT CAST( (( extract(years from $1) * 12 ) + extract(months from $1))
AS text ) || '' months'';
' LANGUAGE SQL IMMUTABLE STRICT;

(above not checked for typos)

I'd swear that you posted your question on pgsql-sql and I posted the
above solution some months ago.

I did post, but this solution did **NOT** get posted. So, I kept my field
as
INT.

BUT, thank you for this.

LER

--
-Josh Berkus
Aglio Database Solutions
San Francisco

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749