• btdt (unregistered)

    Actually, there is nothing wrong with counting whole pennies only. Is kinda nice when you want to ensure that you don't lose any. The problem is that somebody decided to use integers, without knowing how to use them.  I mean  (amount115)/100 + ( (amount115) %100 > 49 : 1 ? 0) is not the prettiest, but the behavior is very clear.


  • (cs) in reply to your guest

    For the record, SQL Server definitely has a fixed-precision type that works for this:

    declare @price decimal( 5,2 )

    set @price = 10
    select @price

    set @price = @price * 1.0001
    select @price

    set @price = @price + 0.001
    select @price

    yields

    10.00
    10.00
    10.00

    How much precision is up for debate :-)

  • Mike (unregistered)

    WHAT?!  My calculations might be off by 0.0000000001% if I use floating point numbers?!  That's a horrible, horrible tragedy!  I will use integers for everything I ever do in the future.  Thanks for setting me on the enlightened path.

  • The Anonymous Coward (unregistered)

    The degree of WTF depends on tool support and context.  It's quite possible that the data representation is not a WTF.

    In particular, on the DATE side (since it's been getting the least critical attention): 

    The YYYYMMDD format is actually the internal representation of a date in Teradata.  (Well, almost; there'a actually a 1900-year offset in there somewhere...)  Of course, this is in theory "behind the scenes", with the DBMS standing between you and the "internal" representation so that it can enforce type checks and date-sanity checks; but then again TD will let you operate directly on the date as an integer.

    The WTF is the unnecessarily-complex routine for performing a simple operation, which is not caused by the integer representation.

  • (cs)

    Having a multiply function is perfectly acceptable...

    if you are writing assembly...

    on an architecture which doesn't have a multiplication operation.

  • (cs) in reply to areyouretarded?
    Anonymous:
    Anonymous:

    BOSH!!!!

    Let me be the first on this post to claim NO WTF.  Considering performance and data integrity, you should always convert things such as dates to two integer keys.  One for the date and one for the time.  The reason why this code is embedded within a SQL
    Function is to abstract this logic from the clueless FTEs that take over the project once the talent leaves.   

    tdog 



    Umm, are you retarded?

    I think that was sarcasm.[|-)]

  • (cs) in reply to E

    Anonymous:
    Just put this into sql server 2005: SELECT [money] = $340.33 * $1.155, [decimal] = 340.33 * 1.155 money = 393.0812 decimal = 393.08115 It rounded correctly to precision of 4 decimal places when US currency is only 2. Would not the proper procedure to be to keep the extra decimals around as long as possible and only round them for the final currency amount spit out to the UI/File/Bill/Whatever?

    Within a function, sure; but, the result of this calculation is only accurate to two decimal places (the precision of the least precise number).  There's an exception when both numbers are known to be exact; but, returning 4 decimal digits would be misleading in most applications, although spreadsheets do it every day.

    For anyone who needs a demonstration of how painful floating point numbers can be, type this into IE's address bar, and hit Enter:

       javascript: alert(0.1 * 0.1)

    In the end, I think there's a WTF in the implementation; but, the idea itself is solid.

  • (cs) in reply to Edgar Plonk

    Edgar Plonk:
    "Cliché" is not an adjective. It is a noun. The adjective is "clichéd", if you really feel compelled.

    That would be "if you ARE compelled" [:)]

  • (cs) in reply to Mike
    Anonymous:

    WHAT?!  My calculations might be off by 0.0000000001% if I use floating point numbers?!  That's a horrible, horrible tragedy!  I will use integers for everything I ever do in the future.  Thanks for setting me on the enlightened path.



    This looks to me like sarcasm.  If so, in the imortal words of Napoleon Dynamite... "IDIOT!"

    In any system that tracks real money you never store that money in a floating point variable.  Never, never, never!  It would be a WTF well beyond the stupid garbage that is the topic of this today's message.  Values of $.01 (very common in most financial apps) will usually cause problems, and if you process any large transactions things can get really screwy.

    Money needs to be stored in a form that is precise.  Integers work (although I'd go with longs over integers), for the most part, foreign currency can make things interesting.  Many programming languages have a Decimal type to deal with this for you.  Most DB's have a Decimal type in which the precision can be set.  Worst case you can store the values as char arrays or strings.

    That being said, (unless I'm missing something about the FLOAT cast), the code in question destroys the precision we assume they were aiming for with the FLOAT cast.

    The Date Integer is a lesser WTF, but most of the problems with that have been mentioned already.
  • Rain dog (unregistered) in reply to kipthegreat
    kipthegreat:
    Alex Papadimoulis:

    The "Dollars" column, despite it's name, really stored pennies.



    Except for being name "Dollars", isn't this how you're supposed to do this?  That's what I was taught in like CompSci 101 anyway.  It's never been an issue for me, and avoids questions like "Why is the sum of seven $0.01 transactions less than $0.07" (or something like that which might be seen in the real world).


    What about the case where you need 7 $0.005 cent transactions?
  • Justin (unregistered) in reply to your guest
    Anonymous:

    Anonymous:
    The real WTF is Mozilla using the backspace key as a shortcut for "go back.."

    Really, the person who did that had to know it was retarded. And whoever did it in IE too.

    [^o)]

    IE works the same way!



    He said that IE did this too ---- "And whoever did it in IE too"
  • (cs) in reply to Edgar Plonk
    Edgar Plonk:
    It is a noun.


    No, sorry, "it" is a pronoun.
  • me from den bosch (unregistered) in reply to Mike

    In accounting that is a serious problem because debit and credit do not match and you cannot close your books. Doing your math is not enough, you also must look carefully at your rounding to avoid that negative numbers round the same way as positive numbers.

    These difference no matter how small are a serious problem in accounting systems.

  • John Hensley (unregistered) in reply to Mike
    Anonymous:
    WHAT?!  My calculations might be off by 0.0000000001% if I use floating point numbers?!  That's a horrible, horrible tragedy!  I will use integers for everything I ever do in the future.  Thanks for setting me on the enlightened path.

    No, not 0.0000000001%. After rounding your calculation is off by one cent. Businesses that record millions of financial calculations per day care about that. Seriously.

  • Justin (unregistered) in reply to John Hensley
    Anonymous:
    Anonymous:
    WHAT?!  My calculations might be off by 0.0000000001% if I use floating point numbers?!  That's a horrible, horrible tragedy!  I will use integers for everything I ever do in the future.  Thanks for setting me on the enlightened path.

    No, not 0.0000000001%. After rounding your calculation is off by one cent. Businesses that record millions of financial calculations per day care about that. Seriously.



    Show me a case where rounding would cause an error of one cent -- without having very large numbers which push the limits of float/double value's precision.
  • Paul (unregistered) in reply to Justin

    An error of one cent may be difficult to reproduce, but one that's not is checking balances. If you subtract one float from another, but one of the floats is a result of some calculations that make it's value slightly different, and then you compare the subtraction result to 0.0, you're going to have a problem.

    E.g. BALANCE = 10.5900000000001 PAYMENT = 10.59, resulting BALANCE is still greater than 0.0, and someone could get sent a bill for $0.00

  • Paul (unregistered) in reply to Paul

    And tack a late fee onto that bill, and you'll really have a problem.

  • (cs) in reply to me from den bosch
    Anonymous:
    In accounting that is a serious problem because debit and credit do not match and you cannot close your books. Doing your math is not enough, you also must look carefully at your rounding to avoid that negative numbers round the same way as positive numbers.

    These difference no matter how small are a serious problem in accounting systems.


    Didn't that happen in Superman III?

    :)
  • (cs) in reply to kipthegreat
    kipthegreat:
    Alex Papadimoulis:

    The "Dollars" column, despite it's name, really stored pennies.



    Except for being name "Dollars", isn't this how you're supposed to do this?  That's what I was taught in like CompSci 101 anyway.  It's never been an issue for me, and avoids questions like "Why is the sum of seven $0.01 transactions less than $0.07" (or something like that which might be seen in the real world).


    A number of DBMSs have a DECIMAL datatype, which is FIXED POINT and thus does not have the problems related to floating-point math and money.


    As for the date garbage, why wouldn't they just use a Unix timestamp?  Seems much easier to me...


    Likewise, quite a few DBMSs have date, time, and combined date and time stamps for datatypes. I can't imagine why you'd want to avoid them except in some weird case where you can't figure out the timezone issues.
  • Erg? (unregistered) in reply to Paul

    If you cap off using the money field and get a precision of 4 decimal places versus converting everything to longs as someone else suggested wouldn't that really only make a difference in very large calculations or percentage/tax calculations that exceed the standard length of most tax percentages?

    I would think this would be entirely situational.  If you have tax rates that exceed 4 decimal places (6.753938%) then you would want to match the precision of the most precise calc.

    In reality, however, tax rates etc do not exceed 4 precision points, so I would say that the money field would work just fine.  The chance of you being off by a penny doesn't seem all that likely considering the previous sql example posted rounded correctly which means it didn't just "chop off" the decimals in the back end, it likely took it out to a relevant precision and then rounded to convert to money.

  • (cs) in reply to Gene Wirchenko
    Gene Wirchenko:
    As for the date garbage, why wouldn't they just use a Unix timestamp?  Seems much easier to me...


    It is troublesome when dates before the epoch are to be represented.



    Not really, time_t is a signed integer (not in the standard, though). Getting too far away from the epoch is a problem.
  • (cs) in reply to Paul
    Anonymous:
    And tack a late fee onto that bill, and you'll really have a problem.


    There apparently have been cases of people being dunned for $0.00 with a threat to send the amount to collection if it is not paid. 

    We can laugh about it, but about the RW impact?  Customers can be lost over such stupid mistakes.  If I got such a bill, I would likely switch to a more clueful company.

    Sincerely,

    Gene Wirchenko

  • Mandell (unregistered) in reply to The Anonymous Coward
    Anonymous:

    The degree of WTF depends on tool support and context.  It's quite possible that the data representation is not a WTF.

    In particular, on the DATE side (since it's been getting the least critical attention): 

    The YYYYMMDD format is actually the internal representation of a date in Teradata.  (Well, almost; there'a actually a 1900-year offset in there somewhere...)  Of course, this is in theory "behind the scenes", with the DBMS standing between you and the "internal" representation so that it can enforce type checks and date-sanity checks; but then again TD will let you operate directly on the date as an integer.

    The WTF is the unnecessarily-complex routine for performing a simple operation, which is not caused by the integer representation.



    When I first saw this, my first thought was that it was done by programmers who were used to representing dates like this:

    10 STRING[8] DATE
    15 STRING[4] YEAR
    15 STRING[2] MONTH
    15 STRING[2] DAY

    (Note: My syntax is probably off, it's been about 10 years since I used this.  The point is being able to directly access the component parts of the date - i.e DATE="20060426";DAY=TOSTRING(TOINT(DAY)+1) results in DATE equal to "20060427").
  • (cs) in reply to Erg?
    Anonymous:
    If you cap off using the money field and get a precision of 4 decimal places versus converting everything to longs as someone else suggested wouldn't that really only make a difference in very large calculations or percentage/tax calculations that exceed the standard length of most tax percentages?


    From other newsgroups, I understand that in Europe, the requirement by law in currency conversion is six decimal places.

    Sincerely,

    Gene Wirchenko

  • (cs) in reply to Gene Wirchenko
    Gene Wirchenko:
    Anonymous:
    If you cap off using the money field and get a precision of 4 decimal places versus converting everything to longs as someone else suggested wouldn't that really only make a difference in very large calculations or percentage/tax calculations that exceed the standard length of most tax percentages?


    From other newsgroups, I understand that in Europe, the requirement by law in currency conversion is six decimal places.

    Sincerely,

    Gene Wirchenko



    There are also currencies that have no decimal places and round to the nearest 5.  (103 => 105, 102 => 100)

    As soon as you get into the realm of foreign currency things get really interesting, as you have to deal with conversions, rounding, precision, and the mountains of legal rules.  I happen to be a programmer in the international shipping world.  Yea for me.  ;)

    By the way.  All the legal rules for dealing with money is the "real" WTF.  :)
  • (cs)

    Dates stored as encoded integers? Aah! I've inherited a database where a timestamp is stored as a presentation string in DD-MMM-YYYY HH:MM:SS.SSS format and a screen that selected records within date ranges. The SQL to do this was hideous. As you can guess, the performance sucked and an index couldn't be added. But at least the field could be displayed as is.

    Oh alright, encoding a timestamp as an integer would allow range selection and working index, making it better than string. Still more painful than using DATE for no benefit.

  • An apprentice (unregistered) in reply to Justin
    Anonymous:
    Show me a case where rounding would cause an error of one cent -- without having very large numbers which push the limits of float/double value's precision.

    Try representing 167 772.17 as a float. For a double it goes to 90 071 992 547 409.93 which seems pretty high but still is not secure (eg. if you are obliged by law to pay four-digits-of-precision tax on total assets of a large company).

  • (cs) in reply to smbell
    smbell:
    Gene Wirchenko:
    Anonymous:
    If you cap off using the money field and get a precision of 4 decimal places versus converting everything to longs as someone else suggested wouldn't that really only make a difference in very large calculations or percentage/tax calculations that exceed the standard length of most tax percentages?


    From other newsgroups, I understand that in Europe, the requirement by law in currency conversion is six decimal places.


    There are also currencies that have no decimal places and round to the nearest 5.  (103 => 105, 102 => 100)

    As soon as you get into the realm of foreign currency things get really interesting, as you have to deal with conversions, rounding, precision, and the mountains of legal rules.  I happen to be a programmer in the international shipping world.  Yea for me.  ;)

    By the way.  All the legal rules for dealing with money is the "real" WTF.  :)


    In China, the yuan is divided into 10 jiao and the jiao into 10 fen.  When I was in China, I only once received conversion from the Bank of China to the nearest fen.  The other times, it was to the nearest jiao (or 1/2 jiao -- Memory fades).  I only once ever saw a price expressed using fen and that was for something that would usually be paid using a card system.

    Sincerely,

    Gene Wirchenko

  • (cs) in reply to An apprentice

    This forum has me thinking -- a great many WTFs stem from the resolving of already solved problems. (E.G. refusal to use perfectly good, tested, built-in datatypes like DATETIME, for dubious reasons, leading inevitably to the resolving of useful things like DATEADD(), then refusing to admit that the re-solution might not actually be as good as the already existing solution, etc. etc.)

    But lemons to lemonade, right? I'm starting the Society for the Reinvention of the Wheel to try to profit from this. Dues can be paid to me using any level of fixed or floating precision (I'm flexible) in any currency.

  • (cs) in reply to Paul

    quotequotequotequotequotequotequote

    E.g. BALANCE = 10.5900000000001 PAYMENT = 10.59, resulting BALANCE is still greater than 0.0, and someone could get sent a bill for $0.00

    quotequotequotequotequotequotequote

    This why 3D engines use a "Epsilon" value to test against zero.

    if ( dx < EPSILON){
      draw( "hit!" );
    }

    Funny that business software also care about float precision.

  • Bailey (unregistered) in reply to kipthegreat
    kipthegreat:


    Didn't that happen in Superman III?

    :)

    Didn't that happen in Office Space? ;)

  • (cs) in reply to My Name

    Anonymous:
    This may be a stupid question, but - isn't it better to store such information in integers (or longs) instead of doubles? Wouldn't that guarantee better accuracy? Or is there some other option? Please enlighten me.

    isn't that the office space/ superman III scam?  1/3 ==.33 cents and a third of a cent into my bank account!

  • The Anonymous Coward (unregistered) in reply to Tei

    Tei:
    This why 3D engines use a "Epsilon" value to test against zero.

    if ( dx < EPSILON){
      draw( "hit!" );
    }

    Funny that business software also care about float precision.

    First, the epsilon comparison is more resource-intensive than a simple check against 0.  I really don't want to install an "accounting accelerator" or somesuch.

    Second, the results of an incorrect calculation in a 3D engine is a slight error in the rendering of one pixel, which might persist for a frame or two and will likely go unnoticed.  Epsilon is set to minimize errors, but that doesn't mean that no errors occur, and that's ok because the consequence of an error is small.  The same does not hold for money-handling apps.

  • (cs) in reply to Scarblac

    Anonymous:
    I admit that I have also built a system that stored money as cents instead of euros.

    However if I had to make a database design for one now I would probably use Decimal, it seems a bit more intuitive.

    Does anybody have a good argument one way or the other, though?

     

    How you handle money is your system is completely up to the bank auditors.  The funny thing is if you round 2.5 cents in Excel it becomes 2.  If you round 2.5 cents in a VBA formula on your excel sheet, you get 3!

    Wait.. this has nothing to do with the quote  ?!?!

  • (cs) in reply to Bus Raker
    Bus Raker:

    Anonymous:
    I admit that I have also built a system that stored money as cents instead of euros.

    However if I had to make a database design for one now I would probably use Decimal, it seems a bit more intuitive.

    Does anybody have a good argument one way or the other, though?

     

    How you handle money is your system is completely up to the bank auditors.  The funny thing is if you round 2.5 cents in Excel it becomes 2.  If you round 2.5 cents in a VBA formula on your excel sheet, you get 3!

    Wait.. this has nothing to do with the quote  ?!?!

    Good old banker's rounding....

    http://en.wikipedia.org/wiki/Banker%27s_rounding

    If we adopt the peso we will have none of these rounding problems, for the value of one peso to one dollar is of far greater precision than any of our data types. 

     

  • OwlPuke (unregistered) in reply to Bus Raker

    When an owl eats a rat or a mouse or a beetle, it is unable to digest all of the bones and fur and shell parts and whatnot. A couple times a day it regurgitates all of this into a little packet of goods that can be found around the bases of trees. This little packet is called a CASTING. It seems some parallels could be drawn between that and the thought process of the developer of today's code.

  • (cs)

    If someone I hired wrote code like this, they'd be needing just as many CASTs for all the places I broke their legs.

    Just kidding, I wouldn't really break their legs.  I'd murder them.

  • (cs) in reply to Otto
    Otto:
    "(1 + CAST(15 AS FLOAT)/100)"
    Is it just me, or is this stupider than normal? What's wrong with typing "1.15" ?


    It's not as bad as it looks.  The execution plan will very likely compile that to a constant anyway.

    There is a slim justification in the expanded version because you can easily do a search-and-replace for "15" to "8" and the expanded version will still be correct.  1.15 on the other hand would become 1.8, which is a hike of 80% instead of 8%.
  • Konrad (unregistered)
    Dates encoded in integer fields ?
     
    thats not how a real enterprise system does dates. A real enerprise system will have
    a table of tays say with a record with every day between 1st January 1900 and the 31st december 2100.  apart from links to a year table, each column will have handy things like various string representations of the date, and which day of the week it is.
     
    If you need time, well you only need 1440 records to represent every minuet of every day.
    then you can enter usefull data like which minuets are on the hour, on the half hour, at 15 minuer intervals and at 10 minuet intervals.
     
    then when you need a date you don't go incoding it you just use a foreign key to the day table. if you need a time as well you use two foregin keys. (if you  need a duration, well all you need is 4 coulumns)
     
    PS I'm not making this up. I am currently maintaing a system that does this, unfortunatly
    that is not its biggest problem.
     
     
  • Konrad (unregistered) in reply to Maurits
    Maurits:
    Otto:
    "(1 + CAST(15 AS FLOAT)/100)"
    Is it just me, or is this stupider than normal? What's wrong with typing "1.15" ?


    It's not as bad as it looks.  The execution plan will very likely compile that to a constant anyway.

    There is a slim justification in the expanded version because you can easily do a search-and-replace for "15" to "8" and the expanded version will still be correct.  1.15 on the other hand would become 1.8, which is a hike of 80% instead of 8%.
     
    you have two options for searhc and replace
    1) replace 1.15 with 1.08 or replace 15 with '08' eitherway there is no problem keep in mind
    search and replace works on stings it dosn't care that the string is a number and will not trim leading zeros.
  • (cs) in reply to Konrad
    Anonymous:
    Maurits:
    Otto:
    "(1 + CAST(15 AS FLOAT)/100)"
    Is it just me, or is this stupider than normal? What's wrong with typing "1.15" ?


    It's not as bad as it looks.  The execution plan will very likely compile that to a constant anyway.

    There is a slim justification in the expanded version because you can easily do a search-and-replace for "15" to "8" and the expanded version will still be correct.  1.15 on the other hand would become 1.8, which is a hike of 80% instead of 8%.
     
    you have two options for searhc and replace
    1) replace 1.15 with 1.08 or replace 15 with '08' eitherway there is no problem keep in mind
    search and replace works on stings it dosn't care that the string is a number and will not trim leading zeros.


    It is still possible to get in trouble.  In C, "08" is an illegal octal constant.

    Sincerely,

    Gene Wirchenko

  • Sammy (unregistered) in reply to Gene Wirchenko

    It is amazing how many people who read this site probably write crap like this all day.   From the 1000 "me 2 me2" comments about floating point to all those who have never actually written sql,  the comments are as bad as the code.

    It wasn't till the second page that someone actually figured out what the sql data type 'decimal' was.   Or the guy who didn't seem to know the difference between money and decimal.  It's mostly in the size ;)  

  • (cs) in reply to Strydyr

    As to that multiplication, multiplying two dollar amounts will yield a quantity whose dimensions are square dollars--which is pretty useless in most situations. So I think that is outside the scope of the issue. (Not to mention that if the quantities weren't known to be exact, you actually have only one decimal--the "least precise" number should be measured by significant digits.)

    And your float*float demonstration works in Firefox and Safari too.

  • (cs) in reply to Sammy

    Anonymous:
    It is amazing how many people who read this site probably write crap like this all day.   From the 1000 "me 2 me2" comments about floating point to all those who have never actually written sql,  the comments are as bad as the code.

    It wasn't till the second page that someone actually figured out what the sql data type 'decimal' was.   Or the guy who didn't seem to know the difference between money and decimal.  It's mostly in the size ;)  

    So, it's finally official: size matters

  • (cs) in reply to silverpie

    And even if you do want to store the number as an integer (something I have to deal with in writing, say, gymnastics-scoring software, where a third of a hundredth can be important to keep precise--fixed-precision decimals are no help there), it's still a WTF to call the column Dollars.

  • just some guy (unregistered) in reply to Konrad
    Anonymous:
    Dates encoded in integer fields ?
     
    thats not how a real enterprise system does dates. A real enerprise system will have
    a table of tays say with a record with every day between 1st January 1900 and the 31st december 2100.  apart from links to a year table, each column will have handy things like various string representations of the date, and which day of the week it is.
     
    If you need time, well you only need 1440 records to represent every minuet of every day.
    then you can enter usefull data like which minuets are on the hour, on the half hour, at 15 minuer intervals and at 10 minuet intervals.
     
    then when you need a date you don't go incoding it you just use a foreign key to the day table. if you need a time as well you use two foregin keys. (if you  need a duration, well all you need is 4 coulumns)
     
    PS I'm not making this up. I am currently maintaing a system that does this, unfortunatly
    that is not its biggest problem.
     
     


    Actually, using a table with pre-calculated date data is a standard practice in data warehouses.  When you're dealing with tables that have billions of rows, joining to a date table is much faster than doing a date calculation on every row.  Perhaps the person who designed your system used to work with data warehouses and didn't understand that this approach doesn't make much sense outside a data warehousing environment.

  • SerGioGioGio (unregistered) in reply to silverpie

    Regarding the DATETIME vs INT field issue, here are possible justifications (my company stores dates as VARCHAR):

    •  DATETIME types (and DATETIME operations) are not portable among all databases systems
    • INT type is sometimes easier to query, say if you want all the records for today: SELECT * FROM my_table WHERE dat = 20060427 ... what's your best SQL equivalent? Again can you run it in all DBMS?
    • Who said he wants to store full /timestamps/ as DATETIME provides? He might merely wants to store /dates/ associated with a record, for example "birthday" or "expiry".
    • DATETIME support has not always been very consistent across all database drivers, although I believe things are improving here.

    If you choose an INT field, of course you will loose DATETIME (proprietary) functions (DATEADD, DATEDIFF, etc), but most of the time anyway, your application should be responsible for such computation.

    Feel free to challenge any of the points above :)

    SerGioGioGio

  • (cs) in reply to Paul
    Anonymous:

    An error of one cent may be difficult to reproduce, but one that's not is checking balances. If you subtract one float from another, but one of the floats is a result of some calculations that make it's value slightly different, and then you compare the subtraction result to 0.0, you're going to have a problem.

    E.g. BALANCE = 10.5900000000001 PAYMENT = 10.59, resulting BALANCE is still greater than 0.0, and someone could get sent a bill for $0.00



    you shouldn't have to worry about sending a bill to a customer for $0.00.  unless the people designing your software are idiots (and I know many are) you would never send a bill out for less than $0.39 (or whatever the cost of a stamp is where you live) and you would probably not want to send out a bill for less than $0.50-$0.60 because of printing, paper, labor costs associated with sending out a bill.  and if you really were using floating point numbers for price (I agree, is stupid) and wanted to send bills to people with any balance then you should use if (balance >= .01) not if (balance != 0.00).   checking equality of floating point numbers is most always a WTF.
  • (cs) in reply to SerGioGioGio
    Anonymous:

    Regarding the DATETIME vs INT field issue, here are possible justifications (my company stores dates as VARCHAR):

    •  DATETIME types (and DATETIME operations) are not portable among all databases systems
    • INT type is sometimes easier to query, say if you want all the records for today: SELECT * FROM my_table WHERE dat = 20060427 ... what's your best SQL equivalent? Again can you run it in all DBMS?
    • Who said he wants to store full /timestamps/ as DATETIME provides? He might merely wants to store /dates/ associated with a record, for example "birthday" or "expiry".
    • DATETIME support has not always been very consistent across all database drivers, although I believe things are improving here.

    If you choose an INT field, of course you will loose DATETIME (proprietary) functions (DATEADD, DATEDIFF, etc), but most of the time anyway, your application should be responsible for such computation.

    Feel free to challenge any of the points above :)

    SerGioGioGio



    Absolutely right about the portability issue.  If you see that it might be possible that you could be changing database types, then the portability of the date time field needs to be considered.  As for getting records for a particular day, or month, or whatever, that can easily be done with datetime type using the proprietary functions available, at least I know I can in mysql (gasp!).

    If you want to store only the date, then you are not thinking very forward.  Space is cheap nowadays, and you have to consider if there is a possibility that you could want the time of the sale as well.  Adding the time functionality later is a lot of extra work than just setting it all up for both later.

    As for the DATEADD functions and such, I hate to lose that functionality, but I never really trusted them for anything but short time steps (I deal with weather data archival, with the possibility of information going back to the 1900s).

    Someone else also mentioned the data validation issue.  Being a mysql person, I feel that my programs should be sending my database valid dates.  Also, this does make it possible to bring in a NaN analogue, NaD (Not a Date).  :-P

    The choice of how to represent dates is one not to be made lightly...
  • (cs) in reply to tster
    tster:
    Anonymous:

    An error of one cent may be difficult to reproduce, but one that's not is checking balances. If you subtract one float from another, but one of the floats is a result of some calculations that make it's value slightly different, and then you compare the subtraction result to 0.0, you're going to have a problem.

    E.g. BALANCE = 10.5900000000001 PAYMENT = 10.59, resulting BALANCE is still greater than 0.0, and someone could get sent a bill for $0.00



    you shouldn't have to worry about sending a bill to a customer for $0.00.  unless the people designing your software are idiots (and I know many are) you would never send a bill out for less than $0.39 (or whatever the cost of a stamp is where you live) and you would probably not want to send out a bill for less than $0.50-$0.60 because of printing, paper, labor costs associated with sending out a bill.  and if you really were using floating point numbers for price (I agree, is stupid) and wanted to send bills to people with any balance then you should use if (balance >= .01) not if (balance != 0.00).   checking equality of floating point numbers is most always a WTF.


    See, that would be the logical thing for a company to do...
    However, I once purchased an item at a jewelry shop on credit.  I paid off the balance the following week at the shop.  A couple of months later, I got a bill for less than 10 cents that was supposedly left on my balance.  I suspect that the cashier entered the cent portion wrong when I paid off the balance, but why the heck did they send me a bill for less than 10 cents?!  It was a jewelry store!  Are they really that hard-pressed for those measly cents to actually pay more to get it?

    Sent back an envelope with some pennies...

Leave a comment on “Functional Multiplication”

Log In or post as a guest

Replying to comment #70063:

« Return to Article