• Razzie (unregistered)

    You'd think some people do these things on purpose, just to piss off some people who might work with their creation someday :)

    A funny story nonetheless!

  • Patrick (unregistered)

    Odd.

    JavaScript stores a time value as the number of seconds since midnight January 1st, 1970. The time value is called epoch time and midnight January 1st, 1970 is the epoch.

  • Martin Crawford (unregistered)

    Only one defence (without knowing the product) database transparency. Acceptable date formats can be inconsistent with each database platform. Portability, etc. I've never used this, but it is vaild enough.

  • Alan Bellingham (unregistered)

    Hey, it's a library system!

    Assuming a 32-bit integer type, that should be valid for several million years.

    Noted, the insert trigger probably won't be working then, but hey, you can't have everything.

  • KDF (unregistered)

    The WTF for me was the 'As a last attempt, I looked at the insert trigger".

    You would really attempt to reverse engineer it using "Binary-Packed Decimal (BPD) format (a relic of the 8086 assembly language)" before looking at the insert trigger ?

  • David Grant (unregistered)

    "I considered that the value was calculated by the noise of a date-weighted satellite beam pointed at Saturn divided by Pi, but then I looked at the code."

  • ak (unregistered)

    Obviously, the developer of this trigger came from the Unix world. There, the time system call returns the number of seconds that passed since Jan 1, 1970 00:00:00, which can then be converted to a human-readable format using a set of library functions. So to me, this is absolutely not a "WTF".

  • Bergstrom (unregistered)

    Yeah, back in my PHP/MySQL days (I'm a .NET guy now), I always stored UNIX timestamps cause doing math was mega easy.

    Course, I was also an idiot programmer, and I know better now. ^_^

    -- Chris

  • Anderson (unregistered)

    it's not a complete WTF- it's a poor design decision.

  • sinclair (unregistered)

    It doesn't matter how other platforms internally represent dates. In Javascript, you don't do the date calculations manually because it has built in support like that. Same with a database ...

  • DCD (unregistered)

    This site isn't funny anymore.

  • Coward (unregistered)

    I think this site "jumped the shark" with the comments WTF.

  • Mohammad Abdulfatah (unregistered)

    KDF and David,

    You might think that my own attempt to crack the format was a WTF by itself, but aren't triggers supposed to just validate the data? Isn't the task of actually inserting and updating the data supposed to be the exclusive domain of the data access tier?

    Call me stupid, or just old school, but that's the way we used to do it.

    -- Mohammad Abdulfatah

  • SC (unregistered)

    "Obviously, the developer of this trigger came from the Unix world. There, the time system call returns the number of seconds that passed since Jan 1, 1970 00:00:00, which can then be converted to a human-readable format using a set of library functions. So to me, this is absolutely not a WTF."

    Note that it is the storing the number of days, not seconds. Try justifying that?

  • T25 (unregistered)

    Triggers are also used for replication, to update aggregate data, to log modifications to tables in other tables, etc, etc. Not just validation.


  • Magnus (unregistered)

    "Note that it is the storing the number of days, not seconds. Try justifying that?"

    Just guessing, but could it be that libraries have little use for keeping track of which second a book was purchased, but the system was expected to be in use after 2038?

  • T25 (unregistered)


    Magnus: his point is that # of seconds is a common, well-defined (if a bit fragile) way of manipulating dates. Making up your own is odd.

    That being said, perhaps the application needed to do alot of date artithmetic on the date values (i.e. <i>if (curdate - take_book_out_date > 14) { fine_user_obscene_amount(); }</i> ) , and storing them as a day count was a really easy way to deal with that kind of requirement, rather than having to deal with converting whatever kind of format the date showed up as in C to something you can do artihmetic on.

    I just hope that they aren't converting to epoch timestamps by multiplying by 86400....

  • ak (unregistered)

    @SC: yes, that's stupid, but is IMHO still excusable by assuming that the programmer was lazy and used to Jan 1, 1970. ;-)

  • Guayo (unregistered)

    I do believe it's a WTF doing that when you have a built in type to handle dates. And I don't think database decoupling applies because he is using a trigger to do it.
    Nonetheless It's also a WTF all that reverse engineering effort without looking at the db triggers first... but hey... who hasn't had those "creativity" momments?... guys?...

  • Pete J (unregistered)

    There's not much mystery to this one!

    The date is being stored in Pick Date format, which is the number of days since 1/1/1970. Negative numbers represent dates before 1970.

    Pick Times are stored as the number of seconds since midnight.

    When you want to show a date you use an output conversion, which is highly configurable so that you can have just the style of date you want - internally the date is just a number, so calculations are a breeze.

    Maybe this system was interacting with, or converted from, a Pick system?

  • ScanIAm (unregistered)

    Um...I'm no SQL genius, but perhaps the issue was memory usage. A 32-bit int takes up 4 bytes. How many bytes does a datetime take up?

    As we quickly approach the point of consumer grade 1 TB memory storage, it's quite easy to forget that memory space used to be an issue.

  • Defiler (unregistered)

    "How many bytes does a datetime take up?"
    At least in Microsoft SQL Server, it takes 8.

  • Anon (unregistered)

    <quote>
    Um...I'm no SQL genius, but perhaps the issue was memory usage. A 32-bit int takes up 4 bytes. How many bytes does a datetime take up?
    </quote>

    A datetime takes 8. A smalldatetime (which is perfectly suitable for this particular task) takes 4. So that doesn't explain it...

  • 5fxn7 (unregistered)


    <i>I guess one can’t trust those database servers to store the date in a more efficient form.</i>

    How is a datetime structure in a database more efficient than the number of days? Doing date arithmetic is infinitely easier with purely additive values like this than it would be with a struct containing the date information. Every time you got the date back, you'd have to convert it to something useable, or screw around with the structure to do the math. I can certainly understand storing the date like this.

    As for the logic in the triggers? As has been pointed out before, there is nothing particularly vile about automatically assigning a creation date in a trigger. It looks like it just puts a datestamp on the row as it's inserted -- a pretty common and mundane use for a trigger, really.

  • Anon (unregistered)

    5fxn7,

    <quote>
    Doing date arithmetic is infinitely easier with purely additive values like this than it would be with a struct containing the date information.
    </quote>

    Huh? Try and add a year for example. Fairly difficult if you are storing number of days since a fixed date (since years have a non-fixed number of days). Damn easy if using datetime / smalldatetime (DateAdd).

    <quote>
    Every time you got the date back, you'd have to convert it to something useable, or screw around with the structure to do the math.
    </quote>

    If you look at the code, the opposite is true. He actually has code to convert it TO his zany system (and he would need to convert it BACK if he ever needs to add years etc etc). If he left it as smalldatetime to start with, he wouldn't need to use DateDiff and DateAdd everytime he wants to treat it as a date.

    And I would love writing SQL to determine which records had been added in February of any year - very easy in normal SQL if you actually stored dates as dates!

    I mean, if you pushed your argument to its logical conclusion, we shouldn't use Date variables in programming either. Much easier just to store them in integers and be done with it.

  • Anon (unregistered)

    Pete J,.

    <quote>
    internally the date is just a number, so calculations are a breeze.
    </quote>

    Obviously SQL Server also stores dates as numbers too. It doesn't actually store '1 Jan 1900' - instead it stores a numeric representation of it (in 4 bytes if smalldatetime, 8 if datetime).

  • ProffK (unregistered)

    Dates and Suburbs. These will haunt the programmer for all of his days.

  • James (unregistered)

    Proffk - I know what you mean. I had a date in the suburbs on 1st Jan 1970 + 12692 and she's still calling me.

  • Filipe (unregistered)

    Looks like a "magic" Y2K fix.

    AKA, take-the-money-and-run

  • Modan (unregistered)

    Like a lot of other WTFs it seems like we need to know more about the problem domain to know if this really is a WTF.

    If for instance the table is Books_Currently_On_Loan then this probably makes sense, as who would ever need to run the query ListBooksLoanedInFeb2003 however you may want to do queries such as ListBooksLoanedForMoreThan7Days.


    Of course in most situations this would not be sensible, but we need to remember that sometimes odd things make some sense.


    Now that I have finished defending it, there still would be a miniWTF left in there, as the name should convey the special meaning or be commented (not sure how many DBs actually allow commenting on fields though). PickDayOfCreation sounds like a good name since googling "Pick Day format" explains what it is, and even without the google at least you know it is something other than a standard date

  • Nick (unregistered)

    I stored dates as the Julien number once as I was writing the app in one country and deploying it in another, it might end up as a WTF on here one day, but it saved me a load of grief when i deployed it

  • DrPizza (unregistered)

    "If for instance the table is Books_Currently_On_Loan then this probably makes sense, as who would ever need to run the query ListBooksLoanedInFeb2003 however you may want to do queries such as ListBooksLoanedForMoreThan7Days. "

    But if you used a proper SQL date both queries would be trivial and use built-in functions.

    He looks to be using SQL Server (judging by datediff), so he could not only do "books loaned for more than seven days":
    select * from [loans] where datediff(day, [loans].[date], getdate()) > 7
    but he could also do "books loaned in feb 2003":
    select * from [loans] where year([loans].[date]) = 2003 and month([loans].[date]) = 2

    So, no, bullshit, it makes no sense at all.

  • ZZZ (unregistered)

    he programmer may not be doing the logic in the SQL -- it's possible all of those calculations are in the application layer.

    Anyway, let's just stop picking on this WTF. It's not a particularly exciting one.

  • noname (unregistered)

    First its BCD (for Binary Coded Decimal), not BPD, second its not a relic of the early x86 but rather one that goes back to cobol and the early IBM mainframes (the only reason the x86 contained assembly-level support for it was because some guy at intel thought it might be useful, after all the PC was developed by IBM)

  • Mohammad Abdulfatah (unregistered)

    noname:

    Thank you, I stand corrected, both on the historical inconsistency and getting the acronym wrong. I have programmed in assembly before on commercial systems. But I was only exposed to the whole BPD thing during my academic study. I guess I should have paid more attention in classes.


    To all the others:

    Okay, I guess I was having a "creative moment" while working on that as Guayo has so aptly put it, and I should have looked at the trigger first. I guess I'm guilty of WTF'ing myself. But then again, let he who is without a WTF cast the first comment.

    Cheers!

  • ML (unregistered)

    Can't you all see that it's just a clever way to encrypt the date?

  • Chui Tey (unregistered)

    This is probably not a WTF. The original code could have been written in Unix and the database could was probably a dbm backend or even just a simple text file. On top of that, layers of forms and reports could have been written.

    When proper RDBMS became affordable, a decision was made to do a minimal port without breaking all the functionality in other modules.

Leave a comment on “I'll use my own, thank you very much”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article