PostgreSQL Rounding

Posted on 2010-01-06
Last Modified: 2012-06-27

I am in the process of creating a trigger in a PostgreSQL database. I need to round the decimal number to the nearest quarter. For instance, 14.68 would be rounded to 14.75. We have a time and attendance system that round items to time and a half. The problem is that when it rounds the total time calculated for overtime it rounds it to an odd decimal such as 14.68. Our company policies dictate that these items be rounded to the nearest quarter of a decimal. The easiest way to do this is on the database end rather than editing the source code of the project. I need a PostgreSQL function that rounds the numbers past the decimal to the nearest .25. Any help would be greatly appreciated.
Question by:jpadkins49
    LVL 1

    Author Comment

    I found the answer. The amounts are stored as epoch numbers within our PostgreSQL database. It does not look pretty, but here is what I got:
    update accrual_balance set balance = cast(round((balance/60)/15.00)/4 as decimal(10,2))*60*60  where id = 8450

    Let me know if there is an easier way.

    LVL 12

    Accepted Solution

    The expression that would perform this rounding is: round(<field_name>*4)/4.0

    So a function implementing this expression is given below.  This function assumes that the column you are interested in working with is of 'decimal' type.  You can change the 'decimal' to be 'double precision' or whichever data type you want as needed.  (Or just use 'anyelement' in both places.)
    CREATE FUNCTION round_to_quarter(decimal) RETURNS decimal
    AS '
      SELECT round($1 * 4)/4.0

    Open in new window

    LVL 22

    Assisted Solution

    by:earth man2
    select cast(round((26820/60)/15.0)*900 || ' seconds' as interval);

    create or replace function r2q( x in interval ) returns interval as $$
      r double precision := round((extract(epoch from x)/60)/15.0)*900;
      return cast( r || ' seconds' as interval);
    $$ language plpgsql;

    treacle=> select r2q('7:27');
    (1 row)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now