On 2007-01-31 [EMAIL PROTECTED] wrote:
Can anyone point me to a section of manual or link how to reduce digits
Lookup the manual for round() but be sure that you understand it if you use
round in financial applications as it might be unexpected for you:
mysql SELECT 25E-1 = 2.5,
Hi,
If you want to round your value to the specified no. of digits, use round().
Else if you want to extract the values without rounding use truncate().
mysql select round(blustat,2) from parts; [the value will get rounded to
the nearest decimal]
or
mysql select truncate (blustat,2) from
Hello,
Can anyone point me to a section of manual or link how to reduce digits
after
a decimal sign?
I have a table
mysql desc part;
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
Dimitar Vassilev asks:
Hello,
Can anyone point me to a section of manual or link how to reduce digits
after
a decimal sign?
I have a table
mysql desc part;
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
Hi All,
I seem to be having a problem with the resolution using the timestamp
function. I am accessing the database snort on an ACID/SNORT/MySQL
installation utilizing a collection of shell scripts that are run as
cron jobs and function as a bot adding and removing firewall rules
(iptables).
Hi All,
I seem to be having a problem with the resolution using the timestamp
function. I am accessing the database snort on an ACID/SNORT/MySQL
installation utilizing a collection of shell scripts that are run as
cron jobs and function as a bot adding and removing firewall rules
(iptables).
Nick Sinclair wrote:
[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)
* ...It only resolves to the DAY and not an hourly resolution. I have
included a script below that I use for debugging, the MySQL
functionality is taken directly from one
Jigal van Hemert wrote:
Nick Sinclair wrote:
[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)
* ...It only resolves to the DAY and not an hourly resolution. I have
included a script below that I use for debugging, the MySQL
functionality
Hi all.
I am trying to figure out if there is a way to configure MySQL so that when
inserts occur on decimal fields where the value being inserted has more
precision than the field specification, then the value inserted is rounded
instead of truncated.
For example, if i have a column:
Acres
on 7/20/05 2:23 PM, sean c peters at [EMAIL PROTECTED] wrote:
Hi all.
I am trying to figure out if there is a way to configure MySQL so that when
inserts occur on decimal fields where the value being inserted has more
precision than the field specification, then the value inserted is rounded
sean c peters wrote:
Hi all.
I am trying to figure out if there is a way to configure MySQL so that when
inserts occur on decimal fields where the value being inserted has more
precision than the field specification, then the value inserted is rounded
instead of truncated.
Rounding is what
This reminded me of one more difference between Windows and Linux/Unix.
MySQL use the round function out of the host libraries. If you are on a
Windows box the rule for rounding is if the column immediately to the right
of the column you are rounding to is a 5 then round up
i.e. make 2.485
that when doing SUM() on
this decimal column, I get a strange rounding error (see below), and was
hoping that someone out there can help me with this.
My recent test involved the following three values from the column:
2.4950
2.5950
2.7700
(Chosen, for this example, as they are precise at few decimal
Huh, you know. Now that I'm not at work and therefore don't have my
numbers to check against, you're right.
Man, I must need more coffee.
Never mind me. :)
May be back tomorrow, though, when I have the numbers in front of me. I
know they didn't add up earlier...
Martin
Hassan Schroeder
a series of SQL statements, and I noticed that when doing
SUM() on this decimal column, I get a strange rounding error (see
below), and was hoping that someone out there can help me with this.
My recent test involved the following three values from the column:
2.4950
2.5950
2.7700
(Chosen
noticed that when doing
SUM() on this decimal column, I get a strange rounding error (see
below), and was hoping that someone out there can help me with this.
My recent test involved the following three values from the column:
2.4950
2.5950
2.7700
(Chosen, for this example, as they are precise at few
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I came upon an alogrithm years ago somewhere that showed how to programmatically round
numbers. Here
it is:
// num is the number we want to round. it may be int or fp.
// what is what we want to round num to (ie nearest 10, 1/100, etc)
// The rounding
Hi
I seem to be having problems returning the expected results when using
the mysql ROUND() function.
Rounding 3.565 to 2 decimal places i would expect to return 3.57
however using
SELECT ROUND(3.565, 2);
it returns 3.56. While using
SELECT ROUND(3.575, 2);
works as expected returning 3.58.
I
[mailto:[EMAIL PROTECTED]
Verzonden: maandag 25 oktober 2004 19:35
Aan: [EMAIL PROTECTED]
Onderwerp: rounding problem
Hi
I seem to be having problems returning the expected results when using
the mysql ROUND() function.
Rounding 3.565 to 2 decimal places i would expect to return 3.57
however
integers depends on the C library implementation. Different
implementations round to the nearest even number, always up, always
down, or always toward zero. If you need one kind of rounding, you
should use a well-defined function such as TRUNCATE() or FLOOR() instead.
http://dev.mysql.com/doc/mysql/en
way to do this.
I read in the manual that this behavior depends on the C library
implementation, but how do you change it? To me, this is not normal
behavior. Can anyone help? Thanks in advance...
As stated in the manual, the behaviour of rounding a final digit 5 varies
with the C library
Can anyone explain why:
SELECT ROUND(.012345, 5)
- .01234
Why doesn't mysql round the 5 up to .01235? How do I get it to round up?
I've tried manipulating ceil, floor, round and truncate and I can't seam
to find an easy way to do this.
I read in the manual that this behavior depends on the C
, and round down if it's even. (This averages out the bias you otherwise
get with 5/4 rounding.) To see if this is what you're seeing, try rounding
0.012335 to 5 places...I bet it'll round up to 0.01234.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe
if the digit before the 5 is
odd, and round down if it's even. (This averages out the bias you
otherwise
get with 5/4 rounding.) To see if this is what you're seeing, try
rounding
0.012335 to 5 places...I bet it'll round up to 0.01234.
--
MySQL General Mailing List
For list archives: http
integers depends on the C library implementation. Some round to the nearest
even number, always up, always down, or always toward zero. If you need one
kind of rounding, you should use a well-defined function like TRUNCATE() or
FLOOR() instead.
Regards
Georg
Mikhail
On 23 Jan 2004 at 12:13, Andrew Kuebler wrote:
Can anyone explain why:
SELECT ROUND(.012345, 5)
- .01234
Why doesn't mysql round the 5 up to .01235? How do I get it to round
up? I've tried manipulating ceil, floor, round and truncate and I
can't seam to find an easy way to do this.
Hi Andrew
Consider using NUMERIC or DECIMAL to maintain precision.
From the manual:
The NUMERIC and DECIMAL types are implemented as the same type by MySQL,
as permitted by the SQL-92 standard. They are used for values for which
it is important to preserve exact precision, for example with
Does anyone have any ideas on how to round a number in the form float(3,4) to
the nearest sixteenth of an inch.
eg ending in
.0625,
.125
.1875
.25
.3125
etc
I haven't found anything in the manual about this, maybe normal people don't
want to do this.
--
Regards
Richard
--
MySQL General
In the last episode (Jan 12), Richard Davies said:
Does anyone have any ideas on how to round a number in the form
float(3,4) to the nearest sixteenth of an inch.
eg ending in
.0625,
.125
.1875
.25
.3125
ROUND(myfield*16)/16
should work.
--
Dan Nelson
[EMAIL
On Monday 12 Jan 2004 22:23, you wrote:
(B Richard Davies wrote:
(B Does anyone have any ideas on how to round a number in the form
(B float(3,4) to the nearest sixteenth of an inch.
(B
(B Multiply by 16, round, divide by 16.
(B
(BObvious really,
(Bupdate table1 set field1 =
Hello,
I need the ability to round off dollar amounts to the
nearest 100th of a dollar amount, IE $14.9564 to $14.96 or
$132.1123 to $113.11
can this be accomplished with MySQL SQL function ??
TIA
--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
At 16:34 -0600 11/22/03, Mike Blezien wrote:
Hello,
I need the ability to round off dollar amounts to the nearest 100th
of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11
can this be accomplished with MySQL SQL function ??
Sounds like a job for TRUNCATE().
Thanks Paul.
but I think the function I was looking for was ROUND(X,D),
the TRUNCATE(X,D) just 'trims' it :)
IE using the value 123.336 and I needed it to round off to
123.34 in this case.
mysql select truncate(123.336,2);
+-+
| truncate(123.336,2) |
Hi!
Use the round() function.
round(14.9564, 2) = 14.96
round(32.1123, 2) = 113.11
Bye
Hello,
I need the ability to round off dollar amounts to the nearest 100th
of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11
can this be accomplished with MySQL SQL function ??
--
Use the ROUND statement
ROUND(sum(CLICKS),sum(IMPS)/sum(CLICKS)*100.0,0),2);
-Original Message-
From: David Garamond [mailto:davegaramond;icqmail.com]
Sent: Sunday, November 03, 2002 2:31 AM
To: [EMAIL PROTECTED]
Subject: rounding behaviour
hi,
mysql select 1/29;
+--+
| 1/29
Kenneth Hylton wrote something about SQL or QUERYs:
I NEVER use float or double to store values like you appear to be doing. I
always used DECIMAL so that they are stored as strings and you do not have
problems associated with what you see here. I then have complete control
over what is stored
From: Michael T. Babcock [EMAIL PROTECTED]
Kenneth Hylton wrote something about SQL or QUERYs:
I NEVER use float or double to store values like you appear to be doing...
FWIW, all of our financial database software stores values in either pennies or
tenths of a cent, not dollars, to avoid
Oh, for BCD floating point.
My first computer with a language (SWTP 6800) had BCD math.
It had something like 6 byte mantissa and a 1 byte exponent.
That would give 11 digits with e +-99
Maybe we should have BCD data types.
Jan Steinman wrote:
From: Michael T. Babcock [EMAIL PROTECTED]
Kenneth
to worry about rounding errors. (unless you're recording
truly enormous sums of money...)
-JF
-Original Message-
From: gerald_clark [mailto:gerald_clark;suppliersystems.com]
Sent: Wednesday, October 23, 2002 10:10 AM
To: Jan Steinman
Cc: [EMAIL PROTECTED]
Subject: Re: Rounding floats
the INTERVAL processing
aatdc so that a fractional formula is calculated BEFORE rounding the values
aatdc in the formula.
I tested your example on 4.0.4 and it works well:
mysql select now(), now()- interval (0.1*60) minute
were more convenient.
How-To-Repeat:
select now(), now() - interval (0.1*60) minute;
Fix:
Perhaps change the order of operations within the INTERVAL processing
so that a fractional formula is calculated BEFORE rounding the values
in the formula.
Submitter-Id: n
]
Subject: rounding?!
Ok here's the problem. Trying to write a select statement to be able
to
round a set of numbers to the nearest quarter of an inch. Here is what
I
have so far:
*---
--
*
SELECT
CONCAT(ROUND
Ok here's the problem. Trying to write a select statement to be able to
round a set of numbers to the nearest quarter of an inch. Here is what I
have so far:
*-*
SELECT
CONCAT(ROUND(inside_length + (wall_thickness * 2),
Try round(value*4,0)/4
Worked on the samples I tried
-Original Message-
From: Nicholas Stuart [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 23, 2002 1:47 PM
To: [EMAIL PROTECTED]
Subject: rounding?!
Ok here's the problem. Trying to write a select statement to be able
to
round
Well, assuming the time is in seconds (as in a UNix timestamp), you
could use rounding function:
select 600 * round(time_in_seconds/600)
(600 being # of seconds in 10 minutes).
Read the docs for the round() function -
http://www.mysql.com/doc/M/a/Mathematical_functions.html
[ob. filter faker: mysql]
For what it's worth, here's an algorithm I use to write my own rounding
function.
To round to the nearest N number of places.
1) divide the number by N
2) add .5 (for negative numbers subtract .5)
3) truncate the result (take integer part)
4) multiply result by N
Description:
Odd numbers round properly at the half (3.5) but even numbers don't (4.5).
How-To-Repeat:
mysql select round(15.5);
+-+
| round(15.5) |
+-+
| 16 |
+-+
1 row in set (0.01 sec)
mysql select round(16.5);
+-+
| round(16.5) |
Hi,
Odd numbers round properly at the half (3.5) but even numbers
don't (4.5).
This sounds like you're misunderstanding an intentional feature. A lot of
applications or system libraries do this to avoid giving 'incorrect' figures
when summing large amounts of rounded numbers.
Here's an
Hello
CREATE TABLE deci (deci decimal(8,2));
INSERT INTO deci VALUES (-2427.88);
INSERT INTO deci VALUES (2427.89);
INSERT INTO deci VALUES (-0.01);
select sum(deci) as s from deci having s0;
+---+
| s |
+---+
| -0.00 |
+---+
Well for the values given above - monetary values -
On 09.03.2001 10:29:28 Christian Hammers wrote:
Is this a bug or is the decimal type just evil or how should *I* behave
when having such a table?
If I'm not mistaken, then decimal is handled internally like a float. And as
such, problems like yours are really expected, because there simply
I think you meant
select sum(deci) as s where deci0;
Christian Hammers wrote:
Hello
CREATE TABLE deci (deci decimal(8,2));
INSERT INTO deci VALUES (-2427.88);
INSERT INTO deci VALUES (2427.89);
INSERT INTO deci VALUES (-0.01);
select sum(deci) as s from deci having s0;
+---+
| s
On Fri, Mar 09, 2001 at 08:13:24AM -0600, Gerald L. Clark wrote:
I think you meant
select sum(deci) as s where deci0;
select sum(deci) as s from deci having s0;
No, I really meant my SELECT. MySQL counted the 3 values together but
instead of coming to the conclusion that the sum is zero it
52 matches
Mail list logo
