Stored float values are inaccurate

gregg.i's profile image gregg.i posted 2 years ago in General Permalink

I tried inserting float values into a database and found that the decimal values were inaccurate. I've been trying to change the length value of the column but it is never stored properly. The decimal values are rounded up/down randomly and when I tried increasing the number of decimal values, it showed that the values being stored varied greatly from the ones I was inserting.

SQL code snippet for creating the database and the values that were stored:

CREATE TABLE IF NOT EXISTS `monthly_offer_financials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `offerid` int(11) NOT NULL,
  `offername` varchar(50) NOT NULL,
  `date` date DEFAULT (last_day(curdate() - interval 1 month) + interval 1 day),
  `total_leads` int(11) DEFAULT NULL,
  `total_income` float(10,3) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1781 DEFAULT CHARSET=latin1;

-- Dumping data for table limemqpmuj_db2.monthly_offer_financials: ~1,734 rows (approximately)
DELETE FROM `monthly_offer_financials`;
/*!40000 ALTER TABLE `monthly_offer_financials` DISABLE KEYS */;
INSERT INTO `monthly_offer_financials` (`id`, `offerid`, `offername`, `date`, `total_leads`, `total_income`) VALUES
    (1769, 0, 'Missing Offers', '2017-01-01', 1, 1047033.062),
    (1770, 0, 'Missing Offers', '2017-02-01', 1, 974809.188),
    (1771, 0, 'Missing Offers', '2017-03-01', 1, 560445.750),
    (1772, 0, 'Missing Offers', '2017-04-01', 1, 376324.500),
    (1773, 0, 'Missing Offers', '2017-05-01', 1, 464542.250),
    (1774, 0, 'Missing Offers', '2017-06-01', 1, 580366.438),
    (1775, 0, 'Missing Offers', '2017-07-01', 1, 749598.688),
    (1776, 0, 'Missing Offers', '2017-08-01', 1, 799277.375),
    (1777, 0, 'Missing Offers', '2017-09-01', 1, 785405.250),
    (1778, 0, 'Missing Offers', '2017-10-01', 1, 882377.188),
    (1779, 0, 'Missing Offers', '2017-11-01', 1, 690341.125),
    (1780, 0, 'Missing Offers', '2017-12-01', 1, 369141.719);

Actual values that were used during insertion:

INSERT INTO monthly_offer_financials(offerid, offername, date, total_leads, total_income)
VALUES
('0', 'Missing Offers', '2017-01-01', '1', 1047033.08),
('0', 'Missing Offers', '2017-02-01', '1', 974809.20),
('0', 'Missing Offers', '2017-03-01', '1', 560445.72),
('0', 'Missing Offers', '2017-04-01', '1', 376324.50),
('0', 'Missing Offers', '2017-05-01', '1', 464542.25),
('0', 'Missing Offers', '2017-06-01', '1', 580366.41),
('0', 'Missing Offers', '2017-07-01', '1', 749598.70),
('0', 'Missing Offers', '2017-08-01', '1', 799277.37),
('0', 'Missing Offers', '2017-09-01', '1', 785405.24),
('0', 'Missing Offers', '2017-10-01', '1', 882377.16),
('0', 'Missing Offers', '2017-11-01', '1', 690341.12),
('0', 'Missing Offers', '2017-12-01', '1', 369141.71)
TTSneko's profile image TTSneko posted 2 years ago Permalink

Because FLOAT should not be used when exact numeric behavior is required. Such as in financial applications, in operations involving rounding, or in equality checks. Source: Technet

gregg.i's profile image gregg.i posted 2 years ago Permalink

Okay then what should the data type be changed to?

Please login to leave a reply, or register at first.