richrock Posted January 5, 2012 Share Posted January 5, 2012 Please be aware - I didn't set this up. Not a problem to alter/edit/nuke though I have a database with a set of price fields for auctions - with starting, buyitnow, reserve, makeanoffer_min and makeanoffer_auto. All of these fields deal with financial amounts. None of the amounts will include pence, so it will never have 1.99, but would be either 1 or 2. All of these fields are set to 'double' in mysql except for the last one (auto) which is set to decimal(10,0). I believe we've spotted a problem where one item has a reserve of £2,012,000 - but the database has stored the reserve as 2. Is this correct for double? I don't realy understand single and double-precision numbers, something I'm off to try and figure out.. But any help on formats for these fields would be great. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 5, 2012 Share Posted January 5, 2012 You can easily test this with CAST-ing. But they should have been been DOUBLES -- rather, DECIMALS. Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted January 5, 2012 Share Posted January 5, 2012 They should all be integers if they're not going to be storing decimal values. And no, a value of "2" is not correct. I bet someone tried to put the comma into the query, causing the query to be malformed. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 6, 2012 Share Posted January 6, 2012 Oh, they'll be decimals soon enough -- I'd rather not have the OP have to change the schema yet again. Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted January 6, 2012 Share Posted January 6, 2012 Well if it's a "real" auction site with bid increments, the company controls the increments and there aren't ever decimals in the bid column. I worked at a "real" auction house and all our bids were round numbers. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 7, 2012 Share Posted January 7, 2012 Well if it's a "real" auction site with bid increments, the company controls the increments and there aren't ever decimals in the bid column. I worked at a "real" auction house and all our bids were round numbers. There's a only a minor storage difference (one byte), so strictly speaking I suppose you're correct. But the fact that dollar amounts are being stored as integers bothers me to no end. Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted January 8, 2012 Share Posted January 8, 2012 True, but with floating point errors some companies (mine included) store even cents as whole numbers. We just divide by 100 when we display them. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2012 Share Posted January 8, 2012 True, but with floating point errors some companies (mine included) store even cents as whole numbers. We just divide by 100 when we display them. There shouldn't be any floating-point errors with DECIMAL -- at least not the same as with DOUBLE / FLOAT -- but I haven't really bothered to check. Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted January 8, 2012 Share Posted January 8, 2012 Not in the DB, no, but in the programming language. We get weird 2.1300000001 results sometimes. They're rare, obviously, but we're a payment processor that works in two dozen currencies, storing everything as whole numbers and storing a "divisor" next to it is safer. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2012 Share Posted January 9, 2012 Not in the DB, no, but in the programming language. We get weird 2.1300000001 results sometimes. They're rare, obviously, but we're a payment processor that works in two dozen currencies, storing everything as whole numbers and storing a "divisor" next to it is safer. Well, yes, all numbers need to be rounded as soon as there's multiplication with decimals. But even the divisor solution won't help with taxes, and such, right? Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted January 9, 2012 Share Posted January 9, 2012 Taxes are where we run into the most problems. We end up with fractions of a rupee or a yen, and they don't do fractional currencies. However, that's why we round. The only time decimals are a real problem is for display purposes, where we have to do weird things. DECIMAL data types are good enough for the most part, since at some point you need to turn a whole number into a decimal for most applications. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.