Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rounding

Rounding

From
"Chris Boget"
Date:
Is there a way, internal in PG, that you can round to the
nearest half decimal (rounded up or down)?  ie, I want to
round 97.37 to 97.5 or 97.81 to 98.
The closest thing I could see in the docs would take the
97.33 turning it into 97.4 and 97.81 to 97.8.  Is there some-
thing I am missing?

thnx,
Chris

Re: Rounding

From
Tom Lane
Date:
"Chris Boget" <chris@wild.net> writes:
> Is there a way, internal in PG, that you can round to the
> nearest half decimal (rounded up or down)?  ie, I want to
> round 97.37 to 97.5 or 97.81 to 98.

Double the value, round to integer, divide by 2?

            regards, tom lane

Re: Rounding

From
Joe Conway
Date:
Chris Boget wrote:
> Is there a way, internal in PG, that you can round to the
> nearest half decimal (rounded up or down)?  ie, I want to
> round 97.37 to 97.5 or 97.81 to 98.
> The closest thing I could see in the docs would take the
> 97.33 turning it into 97.4 and 97.81 to 97.8.  Is there some-
> thing I am missing?
>

I don't know of a built in way to "round to the nearest half increment",
which it seems you want to do. You could always write a function for it.
e.g.:

create or replace function nearesthalf(float8)
returns float8 as '
select case
             when ($1 - floor($1)) <= 0.25 then floor($1)
             when ($1 - floor($1)) > 0.75 then ceil($1)
             else (ceil($1) + floor($1)) / 2
        end
' language sql;

regression=# select nearesthalf(97.17);
  nearesthalf
-------------
           97
(1 row)

regression=# select nearesthalf(97.37);
  nearesthalf
-------------
         97.5
(1 row)

regression=# select nearesthalf(97.67);
  nearesthalf
-------------
         97.5
(1 row)

regression=# select nearesthalf(97.77);
  nearesthalf
-------------
           98
(1 row)

Is this what you were looking for?

Joe

Re: Rounding

From
Josh Berkus
Date:
Chris,

> Is there a way, internal in PG, that you can round to the
> nearest half decimal (rounded up or down)?  ie, I want to
> round 97.37 to 97.5 or 97.81 to 98.
> The closest thing I could see in the docs would take the
> 97.33 turning it into 97.4 and 97.81 to 97.8.  Is there some-
> thing I am missing?

No, but you could easily write your own function.   For example:

CREATE FUNCTION round_to_half ( NUMERIC )
RETURNS NUMERIC AS '
SELECT  (ROUND(($1 * 2))) / 2;
' LANGUAGE SQL IMMUTABLE STRICT;

--
Josh Berkus
Aglio Database Solutions
San Francisco