?
Solved

PostgreSQL Rounding

Posted on 2010-01-06
3
Medium Priority
?
1,194 Views
Last Modified: 2012-06-27
All,

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.
0
Comment
Question by:jpadkins49
3 Comments
 
LVL 1

Author Comment

by:jpadkins49
ID: 26193025
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.

Thanks
0
 
LVL 12

Accepted Solution

by:
cminear earned 1000 total points
ID: 26193324
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
' LANGUAGE SQL;

Open in new window

0
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 1000 total points
ID: 26270054
select cast(round((26820/60)/15.0)*900 || ' seconds' as interval);

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

treacle=> select r2q('7:27');
   r2q
----------
 07:30:00
(1 row)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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: http://www.postgresql.org/ (http://www.postgresql.org/) 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.
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses
Course of the Month8 days, 5 hours left to enroll

616 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