• (disco)

    What did I just say half an hour ago? :laughing: https://what.thedailywtf.com/t/practical-validateion/49659/3?u=raceprouk

  • (disco)

    Andrew's version will not work (generate a SQL exception) when run on 29 February.

  • (disco) in reply to PleegWat

    False, at least for Oracle:

    When the day component of start_datetime is the last day of the month or when the returned month has fewer days, then the returned day component is the last day of the month.

  • (disco) in reply to David_Taylor
    David_Taylor:
    False, at least for Oracle:

    This is such an obvious issue that a date library that didn't handle it would be a wtf. It's not only leap years, but any 31st of the month where the target month has 30 days (or fewer for February.)

    Gilbert and Sullivan:(Pirates of Penzance) Although for such a beastly month as February 28 days as a general rule are plenty One year in every 4 its days shall be reckoned as nine and twenty. By a singular coincidence, I wouldn't be surprised if it was owing to the agency of an ill-natured fairy You are the victim of this clumsy arrangement being born on the 29th of February...

    The unfortunate person thinks he is 21 but has only had 5 birthdays, and so is only 5 and a little bit over [in the non-real world context of comic opera logic. Edited to keep @anotherusername happy]. W S Gilbert would have loved computers.

  • (disco)

    TRWTF is

    Andrew stared at the function for a solid thirty minutes before reaching for the delete key

    I mean, surely 30 seconds is enough to know that hand-rolled date arithmetic should be nuked from orbit?

  • (disco) in reply to Julia

    It was; the other 29.5 minutes was what it took to recover from the shock ;)

  • (disco) in reply to RaceProUK

    Andrew should start reading The Daily WTF regularly. I do and it took me less than a minute to mentally recover from this article.

  • (disco)

    I have an aversion to putting Business Logic where I cannot access it, especially embedded in queries (which is what I am assuming is what's happening here. As indicated by this prose from the article):

    ...so it was up to Andrew to dive into the seedy world of Oracle databases :heart: and debug the function...

    Whilst my chosen language does have a sophisticated date / time API, sometimes I feel it is not worth the overhead of it to perform a simple calculation. Which is one of the reasons why I like to use Epoch (Caveats about 1970 and 2038 and excessive leap years not withstanding):

    epoch.then = epoch.now() - interval.seconds
    

    Thus, as in the solution in the Article, interval.seconds can become a configuration setting (Because Business Logic changes, and I don't have to patch my Code. Mind you, this would apply to any decent solution.

  • (disco) in reply to David_Taylor

    Well what do you know you're right. I must've been misremembering.

  • (disco)

    Article about the clock pictured in the story. http://www.timhunkin.com/a130-tobar-clock.htm

  • (disco) in reply to kupfernigk

    "is only 5 and a little bit over" is incorrect; he was in fact 21 years old, but his contract was worded so as to release him when he had attained to a certain number of birthdays, not a certain age in years per se.

  • (disco) in reply to Kate_Lineker

    Very cool, thanks for the link!

  • (disco) in reply to anotherusername
    anotherusername:
    "is only 5 and a little bit over" is incorrect;

    I was quoting the libretto. We are told that he is "five and a little bit over", and he complains that "now I am a little boy of five"

    It's no good arguing with what Gilbert actually wrote. Of course it is incorrect in the real world but this is the world of comic opera. You'll be telling me next that the Lord Chancellor isn't allowed to change the law simply by writing in the law books, or that there really was a town in Japan where the executioner really couldn't execute someone until he had executed himself first.

  • (disco) in reply to loose
    loose:
    Which is one of the reasons why I like to use Epoch (Caveats about 1970 and 2038 and excessive leap years not withstanding):

    For accounting code where everything goes by months and years, I like to include a timestamp field in records which is simply the month since a given epoch - e.g. Jan 1900 (or Jan 2000 depending on application) = 0. This plays nicely with Java and makes extracting accounting date ranges extremely easy, as well as reducing query overhead since only a short integer has to be compared (unless you think your code will last for thousands of years.)

  • (disco) in reply to kupfernigk

    I have this argument a lot. I think the code should produce a precise and efficient query as it can because of the reason I stated above and because it reduces the load on the DB. There are those that argue you don't need to do this because the DB is better at doing / handling ....whatever because they were "designed" for it.

    My response / attitude is: Scalability as the relationship between the DB and Code (instances) is One to Many. In virtually all instances, there is the resource of an entire PC to mangle the code, and only one instance of a DB to provided (even if your DB is a cluster farmfuck) the data.

  • (disco) in reply to kupfernigk

    No fear of that; I don't know the script of Pirates near well enough to argue with what Gilbert actually wrote. It's an acceptable use of artistic license for him to make the character say that, but still factually incorrect.

  • (disco) in reply to kupfernigk
    kupfernigk:
    a town in Japan where the executioner really couldn't execute someone until he had executed himself first

    They do things a little different over there, including the barber paradox.

  • (disco) in reply to loose
    loose:
    There are those that argue you don't need to do this because the DB is better at doing / handling ....whatever because they were "designed" for it.

    I seem to have encountered an increasing number of programmers who think that optimising is a waste of time. They can't surely all have shares in server and storage companies?

  • (disco) in reply to kupfernigk
    kupfernigk:
    For accounting code where everything goes by months and years,

    Calendar months, and years are often different from Fiscal months (which may end on the last Friday of a Calendar month as a common example)....Also fiscal months change over time.... KEEP THE FULL DATE!

  • (disco)

    I found this shocking... It's not like we are talking about SQL Server!!!! :grinning: "...dive into the seedy world of Oracle databases..."

    This is not an accurate statement since the add_months function would account for leap years. Hence it would not set the date to February 29, 2001, February 29, 2002, February 29, 2003, February 29, 2005, February 29, 2006, ......February 29, 2100, etc. "Andrew's version will not work (generate a SQL exception) when run on 29 February."

    Rather than just truncating to midnight on the current day, it would be better to keep whole months or whole quarters depending on what the business requirements are. The first option only needs to get run once per month, and the second only needs to get run once per quarter.

    add_months(TRUNC(sysdate, 'MM'), -72)
    add_months(TRUNC(sysdate,  'Q'), -72)
    

    I do find it shocking that people would rather do a weak and crappy implementation of something that works, rather than using something that works and is easier to maintain. Another case of RTFM or read the fine manual.

  • (disco) in reply to anotherusername

    As someone born in a leap year almost on leap day this has always driven me crazy. I would still ageing during the years I wouldn't have a birthday so my age does not change.

  • (disco) in reply to loose
    loose:
    Which is one of the reasons why I like to use Epoch (Caveats about 1970 and 2038 and excessive leap years not withstanding):

    Don't forget leap seconds. Like the one next week.

  • (disco) in reply to Protoman

    I plan to make the most of my extra second in bed :smile:

  • (disco)

    Oh, I know this one.

    Some people, when confronted with a problem, think “I know, I'll write my own time and date handling functions.” Now they have more problems than they can count, and a lineup of angry maintenance programmers waiting by their desk with baseball bats.

  • (disco) in reply to kupfernigk
    kupfernigk:
    David_Taylor:
    False, at least for Oracle:

    This is such an obvious issue that a date library that didn't handle it would be a wtf. It's not only leap years, but any 31st of the month where the target month has 30 days (or fewer for February.)

    Are there many other months (aside from February) that have different numbers of days in different years? (The requirement is to move 6 years, i.e. that the start and end months are the same, and if you do that, only one source day every four years(1) is problematic, although it is debatable whether subtracting a year from Feb28 of the year after a leap year should land on the 28th or the last day of Feb.)

  • (disco) in reply to Steve_The_Cynic
    TheCPUWizard:
    kupfernigk:
    For accounting code where everything goes by months and years,

    Calendar months, and years are often different from Fiscal months (which may end on the last Friday of a Calendar month as a common example)....Also fiscal months change over time.... KEEP THE FULL DATE!

    Doesn't mean you can't keep both, but yeah, someone always ends up demanding more detail at some point.

    Steve_The_Cynic:
    Are there many other months (aside from February) that have different numbers of days in different years? (The requirement is to move 6 years, i.e. that the start and end months are the same, and if you do that, only one source day every four years(1) is problematic, although it is debatable whether subtracting a year from Feb28 of the year after a leap year should land on the 28th or the last day of Feb.)

    Not in different years, but when you're subtracting 3 or 6 months, you're right back where you started.

  • (disco)

    PROTIP: If your code contains comments about leap years

    :
    /* Check for leap year */

    You're doing it wrong.

  • (disco) in reply to Steve_The_Cynic

    Not many, but September of 1752 comes to mind.

  • (disco) in reply to Steve_The_Cynic
    Steve_The_Cynic:
    it is debatable whether subtracting a year from Feb28 of the year after a leap year should land on the 28th or the last day of Feb

    After a quick test, it seems oracle opts for the latter: Adding a month to 28 Feb 2015 gives 31 Mar 2015.

  • (disco)

    That code of subtracting 6 * 12 months isn't going to work if sometime in the future we have a leap-month.

  • (disco) in reply to george_gonzalez

    Damnit you're right! Then our ordering system might get into real trouble. Do you have a PHP-example of the lines I'd have to add to my get_fiscalYear() function?

  • (disco) in reply to TheCPUWizard

    Just to make life really fun. The number of days you need to have in a week before it becomes the first fiscal week of the year vary depending on where you are in the world.

  • (disco) in reply to John_Imrie
    John_Imrie:
    The number of days you need to have in a week before [the week] becomes the first fiscal **month** of the year

    er...?

  • (disco) in reply to Yamikuronue

    Thanks for flagging that up. Correction applied.

  • (disco) in reply to John_Imrie

    Ah, that makes more sense. Thanks :)

  • (disco) in reply to DCRoss
    DCRoss:
    Some people, when confronted with a problem, think “I know, I'll write my own time and date handling functions.” Now they have more problems than they can count, and a lineup of angry maintenance programmers waiting by their desk with baseball bats.

    Baseball bats don't seem sufficient. I'd rather use one of these:

    [image]
  • (disco)

    So who's the submitter, Cho or Andrew? Or is it a joint submission?

  • (disco) in reply to Spectre

    Andrew. Cho wrote the WTF code and then went on vacation.

  • (disco) in reply to george_gonzalez
    george_gonzalez:
    That code of subtracting 6 * 12 months isn't going to work if sometime in the future we have a leap-month.

    Does Oracle have any support for the Hebrew calendar? If you subtracted 12 months from 1st of Adar 5775, you'd get 1st of Adar II 5774 instead of 1st of Adar 5774.

  • (disco) in reply to abarker

    nice, but needs mroe weight behind it to really drive the point home:

    [image]
  • (disco) in reply to accalia
    accalia:
    nice, but needs mroe weight behind it to really drive the point home:

    Weight isn't as important if you can get sufficient speed. :P

  • (disco) in reply to abarker

    In the interests of the most efficient momentum gain, that is true

  • (disco) in reply to RaceProUK
    RaceProUK:
    What did I just say half an hour ago?

    Nevertheless you seem to will have said it in the future.

  • (disco) in reply to DCRoss

    It would be nice if these people then didn't just write the code, but also wrote down the exact spec what that code is supposed to do. So when that poor maintenance programmer needs to fix it, he or she doesn't have to figure out what bizarre behaviour is a bug and what bizarre behaviour is as required.

  • (disco) in reply to TheCPUWizard
    TheCPUWizard:
    Calendar months, and years are often different from Fiscal months (which may end on the last Friday of a Calendar month as a common example)....Also fiscal months change over time.... KEEP THE FULL DATE!

    Did I say I didn't? No...I said "I like to include a timestamp...". It's possible that accounting systems are based around unusual calendar periods - but when reporting on historic trends of sales, purchases, or resource consumption, in SMEs, calendar months are usually good enough. If they aren't, you might want to consider what value you are adding by an expensive query. In my experience of accounting systems, enterprise ones that handle special calendar periods save those periods in a fiscal month based form during posting, so they do the same job that I was doing with my reporting timestamp. Obviously transaction records have a full timestamp. However, if the system knows what fiscal period it is in, there is no technical problem with adding a fiscal period timestamp for future reference - if that helps with reporting.

  • (disco) in reply to loose
    loose:
    Which is one of the reasons why I like to use Epoch (Caveats about 1970 and 2038 and excessive leap years not withstanding):

    So you're going to exclude the very reasons why your code is shit from a critique of your code? You're a terrible programmer and you write shit code. Stop doing that.

  • (disco) in reply to kupfernigk
    kupfernigk:
    optimising is a waste of time

    The kind of optimisation being discussed here (offloading month/quarter/whatever extraction from a date from the database to the application) IS a waste of time in the vast majority of cases. I have never encountered a situation where the normal database load is too high. That only ever happens when somebody messes up a query or an index gets dropped or whatever. You are advocating data redundancy but I bet you didn't measure the difference in performance.

  • (disco) in reply to another_sam
    another_sam:
    So you're going to exclude the very reasons why your code is shit from a critique of your code?

    If we were to attempt to abstract any sense from this statement, in the context of this topic. And let's face it, there is no other context. We would be left with:

    I don't (exclude) roll my own date / time functions (reason) in my code because it's considered to be bad practice (critique).

    It then becomes patently clear that such an abstraction, or even any attempt to "spell it out", would be totally wasted in any direct reply. As the mind behind the statement would, for various reasons e.g. bigotry, ignorance or a complete inability to comprehend, be a complete :barrier: to accepting it.

    Proof of the above is partially evidenced in the following statement:

    another_sam:
    You're a terrible programmer and you write shit code.

    Which is an opinion that would seem to be based on a complete absence of any examples of either programming or code.

    As for:

    another_sam:
    Stop doing that.

    It's not clear to whom this is directed at. The obvious target would be the same target as the statements are aimed at, but there is an indication that it may be some "escaped" internalisation. In which case, there is hope.

  • (disco) in reply to loose
    loose:
    epoch.then = epoch.now() - interval.seconds

    Which is wrong.

    1. Months do not have the same number of days.
    2. Days do not last the same number of seconds - even on average.

    Thus you will be landing at an unexpected point in time, the cutoff is not when you expect.

  • (disco) in reply to another_sam
    another_sam:
    I have never encountered a situation where the normal database load is too high.

    Well, lucky you then. To live in a world where people doing business reporting never mess up queries or fail to index correctly. Where DBAs want to release enough resources to do the job properly. I would have liked to live in your world, it would have saved me a lot of inconvenience.

Leave a comment on “A Convoluted Time Machine”

Log In or post as a guest

Replying to comment #:

« Return to Article