• EXPERT PROGRAMMER (unregistered)

    I HAVE ACHIEVED SATORI

  • Jan (unregistered)

    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.

  • akatherder (cs) in reply to Jan
    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.

    It seems like every DBMS has their own quirks with handling dates. I think it's worth learning these quirks in order to take advantage of the cool built in date functionality.

    I wouldn't be surprised if the dates are actually stored based on the scheme you use. It would drive me nuts not to have a "meaningful" looking date when I am scrolling through a table.

  • Licky Lindsay (cs) in reply to Jan
    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.

    That is actually related to one of my favorite WTF's I've ever seen: somebody did what you described, and stored it in a column with type NUMERIC(12).

    Of course, 1,000,000,000,000 milliseconds after 1/1/1970 turned out to be only a couple of years after the application was released. You'd think someone what have suspected something was up when they noticed that all the timestamps were things like 987654321098.

  • jimi (unregistered)
    STR_TO_DATE() is available as of MySQL 4.1.1.
    If they're running something older than this, they should upgrade anyway. The real WTF is that no one knows how to roll updates into a production environment properly.
  • Bejesus (unregistered)

    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.

  • ObiWayneKenobi (cs) in reply to Bejesus
    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.

  • Licky Lindsay (cs) in reply to akatherder
    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 example.

  • Robert (unregistered)

    Just change the system (e.g., in a testbed) to use the correct date type, and then convert the damn database tables and have it over with. Annoying, yes, but hardly "not feasible".

  • Licky Lindsay (cs)

    I have to admit that if I was facing this I would put the query results in an ArrayList, convert the strings to Dates, and do the sorting in memory. Yes, the database would still be designed in a fucked up way. Yes, it would be slightly less efficient. But it would get me all kinds of praise for having done the "infeasible".

    Of course I'm thinking in Java. Maybe this truly is infeasible in PHP?

  • Claxon (unregistered)

    I agree with Bejesus. A WTF yes, but one easily solved. Even if they refuse to upgrade MySQL, the strtotime function in php should make short work of it. All you need is to grab every record in the table then parse with php. To get that "Working" (although possibly slowly depending on the size of the table), would take maybe 10-20 minutes. Making it fast(er) would probably take longer, but a 20 minute task certainly feel feasible to me.

    I suspect other reasons for not renewing the contract....

  • Claxon (unregistered) in reply to Licky Lindsay
    Licky Lindsay:
    Of course I'm thinking in Java. Maybe this truly is infeasible in PHP?

    Oh trust me it isn't. The reason everyone mocks php is because any idiot can write in it, and make things happen. So no need to deal with all of those tricky java things like... Types.

  • Andrew Cook (unregistered)

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

  • Michael (unregistered)
    That is actually related to one of my favorite WTF's I've ever seen: somebody did what you described, and stored it in a column with type NUMERIC(12).

    Of course, 1,000,000,000,000 milliseconds after 1/1/1970 turned out to be only a couple of years after the application was released. You'd think someone what have suspected something was up when they noticed that all the timestamps were things like 987654321098.

    The author probably meant seconds since the epoch, not milliseconds. Using that, 12 bytes is more than enough space.

  • Boing Oing! (unregistered)

    i've seen a lot of schemas where all columns are varchars, sometimes id is an integer. most developers say they hate the old school rdbms. but they are not using them well :((

    btw: select * from yourtable order by to_date(date_col,'MON YYYY HH24...');

  • TickleMeElmo (cs) in reply to ObiWayneKenobi
    This not being an isolated example, Dave soon decided that it “was not feasible” to renew the contract.
    Here's the WTF.

    Hey, Mr. Prima Donna, good luck with finding that magical place where all the legacy code is perfect.

  • rbowes (cs) in reply to Jan
    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. :)
  • Chris (unregistered) in reply to ObiWayneKenobi

    Hopefully for that place, there just might be a simple way to transition over (maybe modifying whatever it is that is repopulates the MySQL database from Oracle periodically to also update a new column with a proper date datatype).

    Such is a standard part of the challenges in working in IT. Hopefully one could layout the arguments and reasons both in technical speak and management/layman's terms behind the change, and pass it to management. They may not agree to it, but at least they will hopefully "see" the reasons.

    Especially since in all reality they will probably go through many more contractors, the far majority of which will probably never communicate the problems with their stuff and do what most everyone else does, which is cut and run after their contract is up.

    I've done it many a time, like probably all of us. Then again, when I feel like management is understanding and capable of listening and agreeing to change (like my current employ), I've settled and enjoyed working through such joyous nightmares. It's great to see the users go from processes that take minutes to run to only a few seconds - they're simply amazed :)

  • Richb (unregistered) in reply to Licky Lindsay

    Interesting.

    SQL Server does this: (From BOL)

    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

    Not so handy for library or museum apps... ;)

    Captcha = damnum ! lol.

  • dlikhten (cs)

    Upgrading for large companies is difficult. Depending on how "Proven" the system is.

    Sure you can preach: Upgrading to version XX from AA will give performance increase, scalability, necessary functionality, fix stability issues, etc. etc. etc...

    However what currently exists has been tested. And "proven" to at least work correctly (if it does). Now if you want to upgrade it means that you must do the following:

    1. Upgrade
    2. Migrate Data (this could be tricky)
    3. Test
    4. More testing.

    The problem is steps 3 and 4 could be quite costly.

    On another note, when any programmer works for you, you need the following:

    1. Someone with some programming experience who you TRUST.
    2. The person from (1) to look over code submitted by your hired programmer or consultant. If the above is not done, that programmer can claim everything is "just the way things are done" and there is nothing you can do about it.
  • brazzy (cs) in reply to TickleMeElmo
    TickleMeElmo:
    This not being an isolated example, Dave soon decided that it “was not feasible” to renew the contract.
    Here's the WTF.

    Hey, Mr. Prima Donna, good luck with finding that magical place where all the legacy code is perfect.

    It doesn't need to be perfect, but if you can't bill by the hour or the customer doesn't accept you billing 20 or 50 hours for seemingly trivial changes, then refusing to work on legacy code that's a festering mess is just sensible.

  • AC (unregistered)

    The real WTF is that their version of MySQL did not support user-defined functions as well.

  • Bluemoon (unregistered)

    It says that the entire database is "repopulated" from an oracle database every day. Doesn't that mean that if you write the correct 'conversion' function (and place it in a new column) you can have the new format converted automatically in one day.

    After that you refactor all code (starting with the sort courses) while the live database holds two columns of dates (one old style, one new style) and finally you throw away the old column.

  • s. (unregistered) in reply to jimi
    Comment held for moderation.
  • brazzy (cs) 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. :)
    Actually, 32-bit ints couldn't hold that value towards the end of January 1970. What you're thinking of is timestamps with second (not millisecond) granularity. The good thing about those is that the transition from 32 bit to 64 bit ints (necessary in 2038) at least won't lead to ambiguous data as happend with Y2K, where someone with a stored birth date of 01-01-98 could either be 9 years or 109 years old.
  • Bejesus (unregistered) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    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 beg to disagree, upgrades are very easy to justify to management. One use of the word "security" is generally all it takes to get them completely on side. Ensure the system is backed up before you upgrade it and all you risk is your time.

    If you're talking about something that needs five nines availability perhaps it's you need to do some dry runs first but in this case we don't appear to be.

  • Sumit (unregistered)

    Hardly a story to qualify as a wtf. The whole emphasis is on the SQL statement and how it could've been replaced with a shorted SQL query.

    It is pretty easy to imagine that the second contractor realised the problem of the datestring sorting and instead implemented a hack to make it work within those limitations.

    As the true solution to this problem is already reported not feasible on that version, it looks like the second contractor was actually an intelligent guy to come up with this query.

    I've seen many such implementations (or ugly hacks) where the system was designed poorly from the ground up. It does not mean that programmers who inherited those systems are not good.

  • Dana (unregistered)

    I dunno, what I would do is create a secondary date column. After every run of the Oracle export, use a cron job or trigger to convert the dates into yyyy-mm-dd, then save them. Then you're just stuck with the job of updating all the the queries to reference the new date column.

  • leo (unregistered)

    Hmm... assuming upgrading wasn't possible, how about this hack:

    1. Create new table with two columns, one for dates in that string format and one with an integer (date_index)
    2. Populate new table with dates for the appropiate period and an ordered value for date_index (roughly 10,000 records for 30 years worth of dates)
    3. Add new table to original query, order results by date_index

    Ugly? Hell yeah, but it gets the job done in 10 minutes, is more legible than the contractor's hack, shouldn't impact efficiency and there's no need to refactor any code at all. Amirite?

  • Licky Lindsay (cs) in reply to leo
    leo:
    Hmm... assuming upgrading wasn't possible, how about this hack:
    1. Create new table with two columns, one for dates in that string format and one with an integer (date_index)
    2. Populate new table with dates for the appropiate period and an ordered value for date_index (roughly 10,000 records for 30 years worth of dates)
    3. Add new table to original query, order results by date_index

    Ugly? Hell yeah, but it gets the job done in 10 minutes, is more legible than the contractor's hack, shouldn't impact efficiency and there's no need to refactor any code at all. Amirite?

    You win the thread.

  • Nodren (unregistered)

    php has a strtotime() function as well, no reason he couldnt fix the column in question, and if he's worried about breaking it, he could use another column side by side.

    ohh and, the real wtf isnt he was using php or mysql, its that the company who decided on php/mysql didnt hire anyone competent enough to create a decent site.

  • Zygo (unregistered) in reply to Licky Lindsay
    Licky Lindsay:
    You'd think someone what have suspected something was up when they noticed that all the timestamps were things like 987654321098.

    Obviously they chose NUMERIC(12) because (10) and (11) gave them weird error messages. ;-)

  • Aleks (unregistered) in reply to Jan
    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.

    Could you, please, provide the number you would store for say, 2009-July-15? Note:

    • I don't care about the time.

    • I do want to be able to compare dates from various time zones (in case that matters ... you can pick your own time zone)

    • Note that there are such things as:

      a) Daylight Saving Time (which we can hope will happen as per any rules but we're never sure whether government will decide to change those ... e.g. abolish them)

      b) Leap seconds

    When you're done with that, please tell me which date is represented by 1,247,615,995,679?

  • Zygo (unregistered) in reply to Licky Lindsay
    Comment held for moderation.
  • Aroth (unregistered)

    If you think THATS bad, a collegue of mine decided that timestamps were a hassle and you would have issues when entering birthdates before 1970 (I asked him why negative values would be a problem but couldn't answer me this 4 years later). Instead, we now have a dayfield which (as you might guess) expresses the date in the number of DAYS after 1-1-1970.

    To make matters worse, this field actually used negative numbers to go back into time (before 1970). Worst part of the whole thing is the fact that hes going to work somewhere else leaving me to slowly phase out the fancy dayfield in favor of the vanilla timestamp (or datefield - depending on the usage).

    With pain in my heart I scheduled the deletion of the dayfield library with all its conversion functions... The good old times...

  • ! (unregistered) in reply to Aleks
    Aleks:
    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.

    Could you, please, provide the number you would store for say, 2009-July-15? Note:

    • I don't care about the time.

    • I do want to be able to compare dates from various time zones (in case that matters ... you can pick your own time zone)

    • Note that there are such things as:

      a) Daylight Saving Time (which we can hope will happen as per any rules but we're never sure whether government will decide to change those ... e.g. abolish them)

      b) Leap seconds

    When you're done with that, please tell me which date is represented by 1,247,615,995,679?

    Let's ask PHP. 2009-July-15 = 1246460400 1,247,615,995,679 = Mon, 08 Oct 2035 12:10:39 -0600

    "ullamcorper"? WTF?

  • spr (cs) in reply to Michael
    Michael:
    Using that, 12 bytes is more than enough space.

    I that the same as int(11) in MySQL?

  • spr (cs)

    How many courses can the university have? This is a "small educational institution". Memory is cheap and CPU's are pretty fast. Just read the results in an array and sort it using PHP. I bet it won't use any more resources than that ugly SQL hack.

    And if the small institution is slashdotted, just cache the course listings. Then you would just have to generate it once every semester...

  • mccoyn (unregistered)

    I'm not surprised by this. Oracle and MySQL have totally different default representations for a date. They also have totally different semantics for converting between representations of a date. Its far easier to get them to interoperate if you use a string, although I prefer yyyy-mm-dd since it sorts properly.

  • Robert S. Robbins (unregistered) in reply to ObiWayneKenobi

    I'm working for a company that still seems to be using Foxpro 2.6 for DOS and RPG II. We are migrating applications to C# and the .NET Framework.

  • mudkip (unregistered) in reply to s.
    Comment held for moderation.
  • Matthew (unregistered)

    Why couldn't he add a DATE column and use PHP to parse the date data into the new column and fix the Oracle logic and then drop the old column, renaming it to the old column name?

  • Andrew (unregistered) in reply to Licky Lindsay
    Comment held for moderation.
  • Zygo (unregistered) in reply to brazzy
    brazzy:
    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. :)
    Actually, 32-bit ints couldn't hold that value towards the end of January 1970. What you're thinking of is timestamps with second (not millisecond) granularity. The good thing about those is that the transition from 32 bit to 64 bit ints (necessary in 2038) at least won't lead to ambiguous data as happend with Y2K, where someone with a stored birth date of 01-01-98 could either be 9 years or 109 years old.

    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.

    Actually it's much more likely that a bunch of mixed 32/64 systems will pass Unix timestamps back and forth, silently truncating the timestamps at the lower 32 bits. The good news is that for 68 years or so it will be reasonably obvious when this happens.

  • Derek W (unregistered) in reply to EXPERT PROGRAMMER
    EXPERT PROGRAMMER:
    I HAVE ACHIEVED SATORI

    I think I am one of the few people who actually gets that joke.

  • dkf (unregistered) in reply to Derek W
    Derek W:
    EXPERT PROGRAMMER:
    I HAVE ACHIEVED SATORI
    I think I am one of the few people who actually gets that joke.
    I'll take it that you'll enlighten the rest of us on that then?
  • magetoo (cs) in reply to mudkip
    mudkip:
    s.:
    I work at a huge web portal corporation, and ...
    Hey guys! I found the Real WTF (tm).
    Quoting the whole post? Yes, that's a WTF indeed.
  • Bo, the ancient mainframer (unregistered)

    Well, it could be even worse (tm).

    I once saw a similar system where dates where stored (in SQL Server) as char strings and we were asked to locate all entries for a given month.

    Unfortunately, the very WTF'ed code inherited the "date to string" format from the individual client machine setting, so, say, "02-04-05" could be:

    Feb 4th 2005 Apr 2nd 2005 Apr 5th 2002

    or other funny combinations (we also saw combinations of four- and two-digit years)

    So, give me all entries for April?

    We gave up - there was no way to tell which of the formally valid interpretations of a date was the correct one. To the best of my knowledge, the company still cannot locate entries for any given month.

    Bo, the ancient mainframer

  • Curtis Burisch (unregistered)

    I've had to work around a similar issue, resulting from importing a 200GB database from an AS/400 system. The import itself was a nightmare -- we were given the schema of tables we had to import, but were not told that the character set was EBCDIC (not ASCII!) and that numerics were packed BCD, with a sign nybble. Anyway, once we got the data into SQL server, the next step was to use DTS to transform the source tables into something properly meaningful. However, we had 5 years worth of data, of which only a subset date-range needed to be extracted. The snafu was that dates were all stored in character strings, over two fields, of the format 'yyyymmdd' (and for good measure the second field held 'hhss', with leading zeros removed! I.e. midnight was represented by '0'). We introduced a new sproc that took the date and time numeric fields, and returned a new DateTime. This is far less unwieldy than pasting the DateTime conversion code into each ORDER BY or WHERE statement. Ultimately, because we were working with around 100M rows, we decided to simply add a new column to the table containing the result of executing that sproc.

  • Mcoder (cs) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    ...although our ("our" meaning developers) definition of "works" differs from theirs.

    And that is the root of almost all of our problems.

Leave a comment on “The Mostest Wrong Datatype”

Log In or post as a guest

Replying to comment #:

« Return to Article