Re: [HACKERS] Bug 1500
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Bug 1500
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Bug 1500
Karel Zak [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Bug 1500
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug 1500
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bug 1500
This has my vote Lorne In [EMAIL PROTECTED], 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. -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug 1500
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 ([EMAIL PROTECTED]) Cuando no hay humildad las personas se degradan (A. Christie) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug 1500
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Bug 1500
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Bug 1500
Lyubomir Petrov [EMAIL PROTECTED] 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
Re: [HACKERS] Bug 1500
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug 1500
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bug 1500
Tom Lane wrote:
Lyubomir Petrov [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bug 1500
Steve Crawford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bug 1500
On Fri, 2005-03-25 at 15:33 -0500, Tom Lane wrote: Lyubomir Petrov [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bug 1500
On Fri, 2005-03-25 at 20:03 -0500, Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] 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)+donernum=1#c290bc55d5e1e6b2 -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bug 1500
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bug 1500
On Fri, Mar 25, 2005 at 12:53:53 -0800, Steve Crawford [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
