From lattim@hotmail.com Tue Oct 9 03:49:58 2001 Received: from hotmail.com (oe25.law11.hotmail.com [64.4.16.82]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f993nvn78413 for ; Mon, 8 Oct 2001 23:49:58 -0400 (EDT) (envelope-from lattim@hotmail.com) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Mon, 8 Oct 2001 20:49:50 -0700 X-Originating-IP: [210.18.24.152] Reply-To: "Saurabh Mittal" From: "Saurabh Mittal" To: Subject: ROUND function ?? Date: Tue, 9 Oct 2001 09:15:42 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0007_01C150A2.F89CBC70" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Message-ID: X-OriginalArrivalTime: 09 Oct 2001 03:49:50.0221 (UTC) FILETIME=[724E3BD0:01C15075] X-Archive-Number: 200110/127 X-Sequence-Number: 5082 This is a multi-part message in MIME format. ------=_NextPart_000_0007_01C150A2.F89CBC70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi,=20 select ROUND(0.5) ; returns 0 select ROUND(1.5) ; returns 2; select ROUND(2.5) ; returns 2; select ROUND(3.5) ; returns 4;....so on .=20 I'm sure you would have figured out what's happening !!! Why ?? How do I get to approximate any number x.5 as x+1 ?? Saurabh =20 ------=_NextPart_000_0007_01C150A2.F89CBC70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
select ROUND(0.5) ; returns 0
select ROUND(1.5) ; returns 2;
select ROUND(2.5) ; returns 2;
select ROUND(3.5) ; returns 4;....so on .= =20
I'm sure you would have figured out what's= =20 happening !!! Why ??
How do I get to approximate any = number=20 x.5 as x+1 ??
 
Saurabh
 
------=_NextPart_000_0007_01C150A2.F89CBC70-- From tgl@sss.pgh.pa.us Tue Oct 9 04:42:44 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f994ghn88049 for ; Tue, 9 Oct 2001 00:42:44 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f994gfI21190; Tue, 9 Oct 2001 00:42:41 -0400 (EDT) To: "Saurabh Mittal" cc: pgsql-sql@postgresql.org Subject: Re: ROUND function ?? In-reply-to: References: Comments: In-reply-to "Saurabh Mittal" message dated "Tue, 09 Oct 2001 09:15:42 +0530" Date: Tue, 09 Oct 2001 00:42:41 -0400 Message-ID: <21186.1002602561@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/129 X-Sequence-Number: 5084 "Saurabh Mittal" writes: > select ROUND(0.5) ; returns 0 > select ROUND(1.5) ; returns 2; > select ROUND(2.5) ; returns 2; > select ROUND(3.5) ; returns 4;....so on .=20 > I'm sure you would have figured out what's happening !!! Why ?? Because the IEEE float math standard says so. Round-to-nearest-even is considered good practice. > How do I get to approximate any number x.5 as x+1 ?? Try FLOOR(x + 0.5) if you really want the other behavior. regards, tom lane From joseph.conway@home.com Tue Oct 9 05:04:51 2001 Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9954on93583 for ; Tue, 9 Oct 2001 01:04:51 -0400 (EDT) (envelope-from joseph.conway@home.com) Received: from femail3.sdc1.sfba.home.com (femail3.sdc1.sfba.home.com [24.0.95.83]) by mail1.hub.org (8.11.3/8.11.4) with ESMTP id f9953lX93517 for ; Tue, 9 Oct 2001 01:03:47 -0400 (EDT) (envelope-from joseph.conway@home.com) Received: from jecw2k1 ([24.0.42.111]) by femail3.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with SMTP id <20011009050344.TVEB18284.femail3.sdc1.sfba.home.com@jecw2k1>; Mon, 8 Oct 2001 22:03:44 -0700 Message-ID: <003a01c1507f$c99cf520$0205a8c0@jecw2k1> From: "Joe Conway" To: "Saurabh Mittal" , References: Subject: Re: ROUND function ?? Date: Mon, 8 Oct 2001 22:03:50 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 X-Archive-Number: 200110/130 X-Sequence-Number: 5085 > select ROUND(0.5) ; returns 0 > select ROUND(1.5) ; returns 2; > select ROUND(2.5) ; returns 2; > select ROUND(3.5) ; returns 4;....so on . > I'm sure you would have figured out what's happening !!! Why ?? > How do I get to approximate any number x.5 as x+1 ?? Looks like a bug to me: test=# select * from pg_proc where proname = 'round'; proname | proowner | prolang | proisinh | proistrusted | proiscachable | proisstrict | pronargs | proretset | prorettype | proargtypes | probyte_pct | properbyte_cpu | propercall_cpu | prooutin_ratio | prosrc | probin ---------+----------+---------+----------+--------------+---------------+--- ----------+----------+-----------+------------+-------------+-------------+- ---------------+----------------+----------------+--------------------+----- --- round | 1 | 12 | f | t | t | t | 1 | f | 701 | 701 | 100 | 0 | 0 | 100 | dround | - round | 1 | 14 | f | t | t | t | 1 | f | 1700 | 1700 | 100 | 0 | 0 | 100 | select round($1,0) | - round | 1 | 12 | f | t | t | t | 2 | f | 1700 | 1700 23 | 100 | 0 | 0 | 100 | numeric_round | - (3 rows) test=# select round(2.5); round ------- 2 (1 row) test=# select round(2.5,0); round ------- 3 (1 row) test=# select round(2.5::numeric); round ------- 3 (1 row) When doing "select round(2.5)" the 2.5 gets cast as a float and the "dround" function is used. When doing "select round(2.5,0)", or "select round(2.5::numeric)", the 2.5 gets cast as a numeric and the function "numeric_round" is used, producing a different result. It looks like "dround" simply calls the rint system function, so I'd guess the issue is really there (and maybe platform dependent?). I do recall at least one interpretation of rounding that calls for rounding a 5 to the even digit (ASTM), so the rint behavior may not be strictly speaking a bug -- but certainly having two different interpretations is. In any case, use "select round(2.5,0)" for now. Hope this helps, Joe From allane@cybaea.com Tue Oct 9 23:34:54 2001 Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f99NYsn19404 for ; Tue, 9 Oct 2001 19:34:54 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f99NI3T51795 for pgsql-sql@postgresql.org; Tue, 9 Oct 2001 19:18:03 -0400 (EDT) (envelope-from news) From: Allan Engelhardt X-Newsgroups: comp.databases.postgresql.sql Subject: Re: ROUND function ?? Date: Wed, 10 Oct 2001 00:16:42 +0100 Organization: http://cybaea.com/ Message-ID: <3BC3855A.9909FE71@cybaea.com> References: <21186.1002602561@sss.pgh.pa.us> X-NNTP-Posting-Host: engelhardt.demon.co.uk:193.237.12.86 X-Complaints-To: abuse@demon.net X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.7 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane , lattim@hotmail.com Content-Type: text/plain; charset=iso-8859-1 Lines: 27 To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from base64 to 8bit by postgresql.org id f99NYtn19405 X-Archive-Number: 200110/138 X-Sequence-Number: 5093 Tom Lane wrote: > [...]Because the IEEE float math standard says so. Round-to-nearest-even > is considered good practice. I learn something new every day. :-) While it is true that IEEE 754 defaults to "round-to-nearest", which means rounding midway points to even, it is slightly confusing that this is implemented in the math library (libc) by the rint function (subject to compiler defaults and fesetround calls) while the round functions ((ll|l)?round(f|l)?) of the library does indeed 'round half-way cases away from zero to the nearest integer', which is also the mathematical behavior. Try % info libc Arithmetic Rounding % info libc Arithmetic 'Arithmetic Functions' 'Rounding Functions' on you local friendly U*ix clone and you shall find enlightenment... The SQL standard seems to leave it implementation dependent ('92, sec 4.4.1): An approximation obtained by rounding of a numerical value N for an T is a value V representable in T such that the absolute value of the difference between N and the nu- merical value of V is not greater than half the absolute value of the difference between two successive numerical values repre- sentable in T. If there are more than one such values V, then it is implementation-defined which one is taken. So PostgreSQL does "the right thing" (again!). Allan.