Thread: to_char(interval) --- done?
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 BerkusAglio Database SolutionsSan Francisco
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 somethingin to_char() code now and I will try check interval problemtoo. Ithink the basic problem is in interval2tm() function -- maybe we willneed some other method how convert intervalto 'tm' struct (theto_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 arbitrarystring. I unsure if SQL standard knows something likethis and writesomething for "interval" only is not good idea (IMHO) if there is a lot ofcode which already know formattingdata 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 andmark 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 currentto_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 meansin 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 BerkusAglio Database SolutionsSan 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