• (cs) in reply to Zygo
    Zygo:
    Until some asshole realizes that a whole lot of legacy code can be kept working if the epoch is reset to some carefully chosen Thursday in 2106...OK, maybe not.

    It will more likely change to some Thursday in 2030. By 2106 people will probably not even remember this overflow.

  • (cs) in reply to mccoyn
    mccoyn:
    Its far easier to get them to interoperate if you use a string, although I prefer yyyy-mm-dd since it sorts properly.

    Using dd-mmm-yyyy as in the codeSOD is totally dumb, but I have been known to use yyyy-mm-dd or yyyymmddhhmmss or whatever. As long as you have the largest items before the smaller ones it sorts fine, and it's also easier to take 'bits' of the date out (unlike using 'number of seconds since an arbitrary date'). So you could sort by date simply by using the first 8 characters of a yyyymmddhhmmss field

    The only thing it doesn't let you do easily is 'date difference' or calculate ages etc, which 'number of seconds' or date/timestamp fields let you do.

  • (cs) in reply to s.
    s.:
    So, "create a bunch of code, leave it working for 5 years until bit rot kills it, rewrite from scratch when it finally dies" IS more cost-efficient than "create pretty software, maintain and keep improving it, always keeping track of the changes and using best practices at all times".

    Until you factor the time users lose with your system. YOU don't do that because they are not from your organization, but don't simply assume your experience extends to everybody.

  • (cs)

    I know this is heresy, but ... PHP and regular expressions work perfectly well. Sorting by string rather than number works perfectly well. Just read into memory, cut the string into three, swap the day and year parts, and do a lookup to convert the month part to a two-digit number (using a twelve-way switch statement: PHP does this really well, too...) and voila!

    Any contractor who can't be bothered to implement some trivial, thin conversion layer to a PHP-based front end loses, big-time.

    Wait ... did I just say that PHP and regular expressions work pefectly well?

  • Island Usurper (unregistered) in reply to real_aardvark

    PHP and regular expressions only work perfectly well if you work perfectly well.

    Some technologies don't even have that much.

  • Anonymous Person (unregistered)

    What happens when they upgrade and the date conversion function finds bad data in the tables. Considering that the data field is a char, who knows how many bad values there are where the -quote- date field -unquote- contains 'Jr' or some other bad data from some long forgotten messed up data import. That can play havoc with some string-to-date conversion functions in some languages.

    Saving dates as character is absolutely one of my worst "WTF" pet peeves. The only reason to not use built-in date data types is either incompetence or willful ignorance.

  • Aleks (unregistered) in reply to !
    !:
    Let's ask PHP. 2009-July-15 = 1246460400 1,247,615,995,679 = Mon, 08 Oct 2035 12:10:39 -0600

    "ullamcorper"? WTF?

    You did not even read the message, let alone think about the issue. Original post states "millis(econds)", not seconds. Also, try to compare that number with values that other people would get ... around the world (note: we are just talking about the date :) )...

  • Anonanon (unregistered) in reply to Andrew Cook

    "Wait... If the "most recent" contractor declared it unfeasible, then how could "another" contractor get it done? :P"

    Perhaps the "mostest recent" contractor is the one we're talking about?

  • James Ingram (unregistered)

    The fix to this is so simple! I don't understand why he walked away. Simply add a field to the table of type DATE. Write a one-off script to convert the existing CHAR fields to date. Run it. Amend the import function to also convert the dates for newly imported records. Run it.

    This will obviously miss a few records (those which have been imported between running the first script and amending the import code). So run the first script again, to be sure. Better put it in a field called "Date2", though, in case overwriting a DATE filed adds the values instead of amending them.

    Now you've got a date field which can be used anywhere. You only have one use right now - the report, but other code can be changed to use it on an as-needed basis.

    Then you can simply check "Date1" - if it doesn't have a value, check "Date2". We ought to add "date3" for any dates amended by the our system (future-proofing in advance is always best). We should update our first script at this point, to populate Date 3 as well, just in case.

    Simple.

  • Smash (unregistered) in reply to James Ingram
    James Ingram:
    The fix to this is so simple! I don't understand why he walked away. Simply add a field to the table of type DATE. Write a one-off script to convert the existing CHAR fields to date. Run it. Amend the import function to also convert the dates for newly imported records. Run it.

    This will obviously miss a few records (those which have been imported between running the first script and amending the import code). So run the first script again, to be sure. Better put it in a field called "Date2", though, in case overwriting a DATE filed adds the values instead of amending them.

    Now you've got a date field which can be used anywhere. You only have one use right now - the report, but other code can be changed to use it on an as-needed basis.

    Then you can simply check "Date1" - if it doesn't have a value, check "Date2". We ought to add "date3" for any dates amended by the our system (future-proofing in advance is always best). We should update our first script at this point, to populate Date 3 as well, just in case.

    Simple.

    Now this is some WTF! Not only you came up with the same idea as a dozen posters did above (the "create date-field" approach), but you also needed 3 fields. Way to go, genius!!

    Wouldn't it be smarter to create Date1, modify the import function, and ONLY THEN run the script that would populate that field? This way you wouldn't lose those few records AND you wouldn't need to check IF Date1 had any value stored AND you wouldn't need Date3 either.

  • bullet (unregistered) in reply to Smash
    Smash:
    James Ingram:
    The fix to this is so simple! I don't understand why he walked away. Simply add a field to the table of type DATE. Write a one-off script to convert the existing CHAR fields to date. Run it. Amend the import function to also convert the dates for newly imported records. Run it.

    This will obviously miss a few records (those which have been imported between running the first script and amending the import code). So run the first script again, to be sure. Better put it in a field called "Date2", though, in case overwriting a DATE filed adds the values instead of amending them.

    Now you've got a date field which can be used anywhere. You only have one use right now - the report, but other code can be changed to use it on an as-needed basis.

    Then you can simply check "Date1" - if it doesn't have a value, check "Date2". We ought to add "date3" for any dates amended by the our system (future-proofing in advance is always best). We should update our first script at this point, to populate Date 3 as well, just in case.

    Simple.

    Now this is some WTF! Not only you came up with the same idea as a dozen posters did above (the "create date-field" approach), but you also needed 3 fields. Way to go, genius!!

    Wouldn't it be smarter to create Date1, modify the import function, and ONLY THEN run the script that would populate that field? This way you wouldn't lose those few records AND you wouldn't need to check IF Date1 had any value stored AND you wouldn't need Date3 either.

    smash,

    it's interesting to try to judge peoples' characters from their posts... rarely is it so easy...

  • Smash (unregistered) in reply to bullet
    bullet:
    smash,

    it's interesting to try to judge peoples' characters from their posts... rarely is it so easy...

    I did not judge his character, just his eagerness to point a solution before refining it to something easier and smaller.

    Was I tough on the fella? Alright, maybe I was, and I'm sorry. But it is better hear it from me than from a coworker or worse yet, his boss.

  • bullet (unregistered) in reply to Smash
    Smash:
    bullet:
    smash,

    it's interesting to try to judge peoples' characters from their posts... rarely is it so easy...

    I did not judge his character, just his eagerness to point a solution before refining it to something easier and smaller.

    Was I tough on the fella? Alright, maybe I was, and I'm sorry. But it is better hear it from me than from a coworker or worse yet, his boss.

    Smash,

    I meant nothing about what you said about the other gentleman, just about what your tone says about who you are... but your response is polite and conciliatory, your willingness to admit you might have been a bit tough on him is certainly an indication of a reasonable and thoughtful person. Perhaps my first impression was wrong...

    Your point about his/her post is probably right, he probably wrote, and submitted that post in a few minutes, without reading every prior post or re-reading it and carefully analyzing what he was about to post. But we all do that at times, and perhaps more so in these type of forums than in other venues. To my mind it's an error best simply ignored. There's an old cliche, which I'm sure I am not remembering exactly correctly, that says more or less, that politeness, and good manners, are the glue that holds society together. Criticising people's actions, or beliefs, or their performance, without embarrassing them, or seeming to attack them, is an art worth pursuing. If a person wishes his criticism to have any positive effect other than making themself appear like an un-civilized know-it-all, it is a necessary skill. There are only a finite number of bridges out there to burn...

    My apologies, and best wishes...

  • Bil (unregistered)

    Oh dear -- this brings back (bad) memories. I did some work for The Hecht Company years ago. Came on board after the first programmer took a night train out of town (literally) to try to finish the reports which, supposedly, was all that was left. Same issue. Date data stored as strings, SSN data stored as a string (with embedded dashes) in one table, as a numeric in another, redundant code (if on network do bunch of calculations, else do same calculations all over again, minus the database record lock), etc. etc. I eventually told them that I could not, in good conscience, continue the work since I knew I'd be taking their money for a system that would never work. They would not consider a re-write, so I left. The next guy convinced them that a re-write was required...

  • (cs) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    Bejesus:
    So upgrade MySQL to a newer version, refactor the tables to use the correct type, and change the SQL in the app.

    A WTF but hardly one that's in any way difficult to deal with.

    Big deal.

    Upgrading is easier said than done in the real world. Management types are usually very paranoid about something, ANYTHING going wrong and breaking during the upgrade, so they'll mandate the use of the old, clunky version since "It works", although our ("our" meaning developers) definition of "works" differs from theirs.

    I once worked for a company that happily was using Foxpro 2.6 (the Windows 3.1 version, although at least two machines were using DOS interfaces) and some ancient programs written over 10 years ago in Delphi 2 or 3, and had no plans to ever upgrade because they were too afraid of something not working, and because the original developer specifically made a core application dependent on manually loading a DBF file.

    summink like

    mysqldump blah -uroot -proot > ~/out.sql

    mysql -uroot -proot -dblah

    ALTER TABLE course ADD coursedate_new DATE NULL

    UPDATE course set coursedate_new = function_that_needs_to_slpit_and_concatinate_the_date_into_the_correct_format(coursedate)

    ALTER TABLE course drop column coursedate_new

  • (cs) in reply to Jan

    You will run into the year 2038 problem

  • darwin (unregistered) in reply to Licky Lindsay
    Licky Lindsay:
    akatherder:
    I wouldn't be surprised if the dates are actually stored based on the scheme you use.

    For reasons that I assume must be related to query optimization, none of Oracle, MS-SQL, or MySql actually stores dates in a simple way such as that.

    For all intents and purposes, Oracle stores its dates as a whole number of days (after some epoch elucidated by your link) and a fractional part of a day.

    So you can write things like SYSDATE+1 to get tomorrow, or SYSDATE-1 to get yesterday.

  • darwin (unregistered) in reply to rbowes
    rbowes:
    Jan:
    I know I will be laughed at, but I have long since decided that using any Date format in DB's gives me more trouble than it's worth and I simply use a Number containing millis since 1970. Surprisingly little conversion has to be done.
    Although I agree, we're getting close and closer to that magical year when 32-bit integers will no longer be able to hold that value. It's going to be y2k all over again, and I prefer to future-proof my code a bit. :)

    I missed where he said he would store the value in a 32-bit integer.

  • Ken (unregistered)

    Um, I don't get why this is so hard. If the MySQL database is populated with data from Oracle every hour wouldn't is be easier to create a new database column in the MySQL database with a date/time field type?

    All the old code could use the wtf column, and the new code could really use a date.

    Or did I miss something?

  • W.J (unregistered) in reply to ObiWayneKenobi

    On other hand : Never touch a running system.

  • PhillipH (unregistered)

    Wow yeah, that is ugly. Not only is the SQL hard to read (major understatement), but it loses the ability to be optimized with an index.

  • Poor Helpless Programmers (unregistered)

    yet another example of the average PHP developer's ability to use the right tool for the job..

    I've seen PHP devs with 10+ years experience store dates as ticks in a string column in mysql. correct me if I'm wrong, but I'm not sure that will sort correctly. even if it does, the continuous date conversion required to make anything of theirs work was enough of a WTF.

    Captcha: opto - I opto never have to do PHP ever again

  • eric bloedow (unregistered)

    reminds me of an old problem: when trying to sort by page number, i wound up with this order: 1,10,11,12...2,20,21...etc. the easiest workaround is to number the first 10 pages "01,02,03"...

Leave a comment on “The Mostest Wrong Datatype”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article