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?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Fri, 21 Mar 2003 15:32:11 -0800
Josh Berkus <josh@agliodbs.com>
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/


Mon, 24 Mar 2003 09:40:46 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: to_char(interval)  --- done?


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


Mon, 24 Mar 2003 04:34:38 -0600
Larry Rosenman <ler@lerctr.org>
Re: to_char(interval)  --- done?
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/


Mon, 24 Mar 2003 11:45:55 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: to_char(interval)  --- done?
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)


Mon, 24 Mar 2003 09:56:53 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: to_char(interval)  --- done?
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/


Mon, 24 Mar 2003 15:15:05 +0100
Karel Zak <zakkr@zf.jcu.cz>
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


Mon, 24 Mar 2003 09:50:27 -0800
Josh Berkus <josh@agliodbs.com>
Re: to_char(interval)  --- done?
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


Tue, 25 Mar 2003 09:28:09 -0800
Josh Berkus <josh@agliodbs.com>
Re: to_char(interval)  --- done?
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/


Wed, 26 Mar 2003 08:01:40 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: to_char(interval)  --- done?
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


Wed, 26 Mar 2003 08:38:58 -0800
Josh Berkus <josh@agliodbs.com>
Re: to_char(interval)  --- done?


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


Wed, 26 Mar 2003 10:46:30 -0600
Larry Rosenman <ler@lerctr.org>
Re: to_char(interval)  --- done?
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


Wed, 26 Mar 2003 08:50:36 -0800
Josh Berkus <josh@agliodbs.com>
Re: to_char(interval)  --- done?


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


Wed, 26 Mar 2003 11:06:43 -0600
Larry Rosenman <ler@lerctr.org>
Re: to_char(interval)  --- done?
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


Wed, 26 Mar 2003 09:53:58 -0800
Josh Berkus <josh@agliodbs.com>
Re: to_char(interval)  --- done?


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


Wed, 26 Mar 2003 12:01:30 -0600
Larry Rosenman <ler@lerctr.org>
Re: to_char(interval)  --- done?