• (disco)

    Ouch! Those hurt.

    (Good article.)

  • (disco)

    Date and time handling is hard, lets make it harder.

    TRWTF is contracting to solve a problem which has already been fixed... Several times.

  • (disco)

    Lines like that make me somehow happy that as I get older they're harder to read.

  • (disco)

    they made the column a varchar2(64)

    I wonder if the application crashes and burns if someone tries to schedule a 9AM meeting...

    Filed under: Sounds like a feature, '9' > '10'
  • (disco)

    I'm not going to try to decipher that (not my job). But I'd say you should convince them to buy something off the shelf. MS likely has a number of products that do what they want. Cheaper than contractors and having someone in house to maintain it.

  • (disco)

    Can we get something done about the CSS on those code blocks?:

    [image]
  • (disco) in reply to PJH

    Norepro on Chrome, Win 8: [image]

  • (disco) in reply to hungrier

    FF34.0.5/Linux here.

  • (disco) in reply to PJH

    Repro on FF 33.1 Win 8 [image]

  • (disco) in reply to PJH

    repro on FF(android): [image]

  • (disco) in reply to aliceif

    I was looking at a totally different image just now. Did you or Discourse do something weird?

    Also, the code wraps on Chrome but not on Firefox on my Ubuntu laptop.

    What does IE do?

  • (disco)

    I doubt this helps any, but I tried to make these lines a little more readable.

        elseif(($this->meetings[0]['startDate']meetings[0]['endDate']>date('Y-m-d') || 
       ($this->meetings[0]['endDate']==date('Y-m-d') && $this->meetings[0]['endTime']>date('H:i')))) 
       || 
       (($this->meetings[0]['startDate']==date('Y-m-d') && 
        ($this->meetings[0]['startTime']meetings[0]['endDate']>date('Y-m-d') || 
        ($this->meetings[0]['endDate']==date('Y-m-d') && $this->meetings[0]['endTime']>date('H:i'))))) 
{ 
   // Do stuff
}

    And

    'SELECT * FROM meetings 
WHERE 
    id!='.$id.' AND 
    (((startDate < \''.$start_date.'\' 
          OR (startDate =\''.$start_date.'\' 
          AND startHour <= \''.$start_hour.'\'))) 
          AND (endDate > \''.$end_date.'\' 
          OR (endDate =\''.$end_date.'\' 
          AND endHour >= \''.$end_hour.'\'))) 
          OR (((startDate > \''.$start_date.'\' 
          OR (startDate =\''.$start_date.'\' 
          AND startHour >= \''.$start_hour.'\'))) 
          AND (endDate < \''.$end_date.'\' 
          OR (endDate =\''.$end_date.'\' 
          AND endHour <= \''.$end_hour.'\'))) 
          OR (((startDate < \''.$start_date.'\' 
          OR (startDate =\''.$start_date.'\' 
          AND startHour < \''.$start_hour.'\')) 
          AND ((endDate > \''.$start_date.'\' 
          OR (endDate =\''.$start_date.'\' 
          AND endHour > \''.$start_hour.'\')) 
          AND (endDate < \''.$end_date.'\' 
          OR (endDate=\''.$end_date.'\' 
          AND endHour<\''.$end_hour.'\'))))) 
          OR (((startDate > \''.$start_date.'\' 
          OR (startDate = \''.$start_date.'\' 
          AND startHour > \''.$start_hour.'\'))
          AND ((startDate < \''.$end_date.'\' 
          OR (startDate = \''.$end_date.'\' 
          AND startHour < \''.$endHour.'\'))) 
          AND ((endDate > \''.$end_date.'\' 
          OR (endDate = \''.$end_date.'\' 
          AND endHour > \''.$end_hour.'\')))))';
  • (disco) in reply to machtyn

    That doesn't really reduce the :scream: very much.

  • (disco) in reply to Keith
    Keith:
    Also, the code wraps on Chrome but not on Firefox on my Ubuntu laptop. 
    <blockquote style="word-wrap:break-word">
<pre>
elseif [...]

    It should probably use white-space:normal for the <pre> tag. I guess Chrome automatically fixes this but not Firefox...

  • (disco) in reply to VinDuv
    VinDuv:
    I wonder if the application crashes and burns if someone tries to schedule a 9AM meeting...

    Or anything that doesn't start on the hour. Oh, that 8:30 meeting you have? Sorry you have to change it to 8 or 9.

  • (disco) in reply to machtyn

    The second one is misleading because of the parens not being matched correctly. I personally format it like this:

    SELECT * 
FROM meetings 
WHERE id! = $id 
	AND (((startDate < $start_date OR (startDate = $start_date AND startHour <= $start_hour))) AND (endDate > $end_date OR (endDate = $end_date AND endHour >= $end_hour))) 
	OR (((startDate > $start_date OR (startDate = $start_date AND startHour >= $start_hour))) AND (endDate < $end_date OR (endDate = $end_date AND endHour <= $end_hour))) 
	OR (((startDate < $start_date OR (startDate = $start_date AND startHour < $start_hour)) AND ((endDate > $start_date OR (endDate = $start_date AND endHour > $start_hour)) AND (endDate < $end_date OR (endDate= $end_date AND endHour<$end_hour))))) 
	OR (((startDate > $start_date OR (startDate = $start_date AND startHour > $start_hour)) AND ((startDate < $end_date OR (startDate = $end_date AND startHour < $endHour))) AND ((endDate > $end_date OR (endDate = $end_date AND endHour > $end_hour)))))

    The first statement looks for any meetings that start before and end after a given meeting The second statement looks for meetings that start during and end during a given meeting The third statement looks for meetings that start before and end during a given meeting The last statement looks for meetings that start during and ends after a given meeting

  • (disco) in reply to VinDuv
    VinDuv:
    It should probably use `white-space:normal` for the `
    ` tag.

    That fixes it for me.

  • (disco)

    I think there are an awful lot of people out there who somehow get into SQL without any proper training, and learn it through looking at the queries generated by Access. These are a whole world of pain on their own but looking at some code, there are obviously people who think that is the right way to do things. The absolute worst case I have ever seen was a single SQL query that ran to 3.6Mbytes - yes, that is megabytes - because hard coded case by case exceptions were written into the query rather than fix the data in the database (even a suitably designed view could have done it.) Oddly, the performance wasn't very good.

    My favorite WTF, though, is that SQL Server didn't even have Dates till 2008, creating really annoying portability issues. Because sometimes you really do want to bound epochs to a day (when for instance you are selecting date ranges from tables with millions of records and an efficient query is needed). Absence of Dates allows programmers to store stuff in ways that require inefficient queries - though not as bad as the one in the article.

  • (disco) in reply to kupfernigk
    kupfernigk:
    Because sometimes you really do want to bound epochs to a day (when for instance you are selecting date ranges from tables with millions of records and an efficient query is needed).

    I think I have yet to see the case where a dedicated Date type would prove to be a significant improvement over a DateTime with time portion zeroed. They're all probably represented by some sort of integer timestamp, and comparisons don't really look whether your range of values is sparse or not.

  • (disco) in reply to Maciejasjmj
    Maciejasjmj:
    I think I have yet to see the case where a dedicated Date type would prove to be a significant improvement over a DateTime with time portion zeroed. They're all probably represented by some sort of integer timestamp, and comparisons don't really look whether your range of values is sparse or not.

    Have you actually tried this on a big data set? In fact SQL Server does not store datetime as an epoch, but as two 32 bit integers. The date integer is based on 1/1/1900, the time integer as from midnight. Oddly, time is stored in intervals of 1/300 sec, not milliseconds. For comparison the two integers could be treated as a 64 bit integer since the concatenation is monotonic. But a Date is stored as a single 24 bit integer. If I wish to use an in-memory table as an index to extract the primary keys of rows which fall in a date range, given that for various reasons the primary keys may not be monotonic with respect to the date, the in memory table using datetime is bigger even if an internal optimisation treats datetime comparison as a 64 bit integer compare. I am not about to go and test this but I would expect that the performance hit would be smaller on a 64 bit machine than a 32 bit machine. Even so, there is a difference due to the limited I/O speed of disk operations.

  • (disco) in reply to Eldelshell
    Eldelshell:
    TRWTF is contracting to solve a problem which has already been fixed... Several times.

    I too have worked for a CEO who supposed that a problem which had been fixed by a large team of people and then been optimised and improved over years could somehow be done better by a couple of new grads in six months. Programming is obviously a lot easier than customer schmoozing.

  • (disco)

    Sending work out to a consultant, what could possibly go wrong?

  • (disco) in reply to VinDuv

    I just submitted a bugfix.

    https://github.com/tdwtf/WtfWebApp/pull/116

  • (disco)

    PHP - bah!

  • (disco) in reply to evandentremont

    @Remy just accepted it; hopefully that fixed it.

  • (disco) in reply to Someone_Else

    ...which obviously can be all summarized as a single case: meetings which start before the given meeting ends and end after the given meeting start

    SELECT * 
FROM meetings 
WHERE id! = $id
AND startDate*1000+startHour < $end_date*1000+$end_hour
    AND $start_date*1000+$start_hour < endDate*1000+endHour
-- I used 1000, because I just want lexicographic order on pairs, and have no idea how to do that in SQL.
-- Thinking more about it... 
-- ...who the hell uses separate columns for hour and date instead of unix timestamp or datetime?

    As to the first snippet of code (the allegedly PHP one), I doubt it even parses correctly, as there is an identifier meetings right after an array subscript operator.

  • (disco)

    Yeah, you get code like that, when looking for range overlaps, if you don't know the secret I discovered.

    To determine if two datetime ranges overlap:

    WHERE RANGE1.END >= RANGE2.START AND
      RANGE2.END >= RANGE1.START

    Looks stupid but it works. "Well, Coyne, how did you arrive at that?"

    Well, first of all, it does require that the ranges be "normal", such that RANGE.START <= RANGE.END, always. Then, start with a WHERE clause that proves two ranges don't overlap:

    WHERE RANGE1.END < RANGE2.START OR
      RANGE2.END < RANGE1.START

    It should be obvious why that works. There's only two possible cases: RANGE1 comes before RANGE2 or vice-versa. Now, to get ranges that overlap, just...

    WHERE NOT( RANGE1.END < RANGE2.START OR
           RANGE2.END < RANGE1.START )

    If the previous version found ranges that don't overlap, this one finds ranges that do. Obvious, right?

    Now, to get rid of the NOT, apply DeMorgan's laws:

    NOT (c1 OR c2)  →  NOT(NOT(NOT(c1) AND NOT(c2)))  →  NOT(c1) AND NOT(c2)

c1 = RANGE1.END < RANGE2.START → NOT(c1) = RANGE1.END >= RANGE2.START

c2 = RANGE2.END < RANGE1.START → NOT(c1) = RANGE2.END >= RANGE1.START

    Combine to yield the original WHERE clause, for normal ranges.

    Now, of course, storing the time in a VARCHAR...well, that is beyond help.

  • (disco) in reply to CoyneTheDup
    CoyneTheDup:
    apply DeMorgan's laws

    Whoo-hoo!

  • (disco) in reply to machtyn

    Thank you for typing all of that. Doing so made it much easier for me to analyze it.

    As best I can determine, the SQL is correct. It is, of course, excessively complex. And it has a few more parenthesis than it needs -- even discounting order of operations.

    Formatted and commented:

    'SELECT * FROM meetings
WHERE
id!='.$id.'
AND
/* The other meeting entirely encloses (or equals) this meeting. */
(
  /* other meeting starts before or equal to the start of this meeting */
  (
    (
      startDate < \''.$start_date.'\'
      OR
      (startDate =\''.$start_date.'\' AND startHour <= \''.$start_hour.'\')
    )
  )
  AND
  /* other meeting ends after or equal to the end of this meeting */
  (
    endDate > \''.$end_date.'\'
    OR
    (endDate =\''.$end_date.'\' AND endHour >= \''.$end_hour.'\')
  )
)
OR
/* The other meeting is entirely contained within this meeting (and equals cases). */
(
  /* other meeting starts after or equal to the start of this meeting */
  (
    (
      startDate > \''.$start_date.'\'
      OR
      (startDate =\''.$start_date.'\' AND startHour >= \''.$start_hour.'\')
    )
  )
  AND
  /* other meeting ends before or equal to the end of this meeting */
  (
    endDate < \''.$end_date.'\'
    OR
    (endDate =\''.$end_date.'\' AND endHour <= \''.$end_hour.'\')
  )
)
OR
/* The other meeting starts before this meeting and ends within this meeting. */
(
  (
    /* other meeting starts before this one */
    (
      startDate < \''.$start_date.'\'
      OR
      (startDate =\''.$start_date.'\' AND startHour < \''.$start_hour.'\')
    )
    AND
    /* the other meeting's end is entirely within our meeting */
    (
      /* other meeting ends after this one starts */
      (
        endDate > \''.$start_date.'\'
        OR
        (endDate =\''.$start_date.'\' AND endHour > \''.$start_hour.'\')
      )
      AND
      /* other meeting ends before this one ends */
      (
        endDate < \''.$end_date.'\'
        OR
        (endDate=\''.$end_date.'\' AND endHour<\''.$end_hour.'\')
      )
    )
  )
)
OR
/* The other meeting starts within this meeting and ends after it. */
(
  (
    /* other meeting starts after this meeting starts */
    (
      startDate > \''.$start_date.'\'
      OR
      (startDate = \''.$start_date.'\' AND startHour > \''.$start_hour.'\')
    )
    AND
    (
      /* other meeting starts before this meeting ends */
      (
        startDate < \''.$end_date.'\'
        OR
        (startDate = \''.$end_date.'\' AND startHour < \''.$endHour.'\')
      )
    )
    AND
    (
      /* other meeting ends after this meeting ends */
      (
        endDate > \''.$end_date.'\'
        OR
        (endDate = \''.$end_date.'\' AND endHour > \''.$end_hour.'\')
      )
    )
  )
)';
  • (disco) in reply to CoyneTheDup

    That reminds me ...

    A former co-worker of mine didn't know that you could check for overlaps like this. So he wrote things the hard way everywhere and - of course - forgot to handle the case where one of the timespans is fully inside the other one. That was awful.

  • (disco)

    Did someone say centered interval tree?

  • (disco) in reply to PJH

    While this does fix the linebreaks the code is still not shown correctly, because the first < is used as the start of a html tag...

  • (disco)

    Has been my daily work for the last 4 weeks...

  • (disco) in reply to VinDuv 
    <blockquote style="word-wrap:break-word">
<pre>
elseif [...]
    VinDuv:
    It should probably use `white-space:normal` for the `
    ` tag. I guess Chrome automatically fixes this but not Firefox...
    `word-wrap: break-word;` is actually for IE5.5-7 compatibility. You're looking for `white-space: pre-wrap;` (and you have to put that on the `
    ` element, of course).
  • (disco) in reply to CoyneTheDup
    CoyneTheDup:
    WHERE RANGE1.END < RANGE2.START OR RANGE2.END < RANGE1.START

    Am I TRWTF or is something wrong here? Range 1: 8am - 9am Range 2: 10am - 11am

    Range1.end = 9am < range2.start = 10am Statement returns true, overlapping is false.

  • (disco) in reply to Yamikuronue

    Heh...I hadn't looked closely, but he reversed it (either put the starts as the first arguments or use greater than).

  • (disco) in reply to boomzilla

    I'm not sure you can do it in one statement. If a meeting is entirely later than another meeting, that meeting's end is after the other meeting's start, but they don't overlap. You need two statements to determine overlap, don't you? With an AND, not an OR. I guess if you were fancy and used an XOR it might work...

  • (disco) in reply to Yamikuronue

    Wait...now I'm looking at what you quoted, and it's your quote that's wrong. I'm not sure what's going on, but I think IHBT. :trollface:

  • (disco) in reply to Yamikuronue

    what about this?

    RANGE1.START <= RANGE2.START
AND RANGE1.END >= RANGE2.END

    to my mind, assuming we have a cross join (value will eventually be in both RANGE1 and RANGE2) that should find overlaps

    of course i'd have to sit down with pen and paper to prove it.....

  • (disco) in reply to boomzilla

    huh?

    CoyneTheDup:
    WHERE RANGE1.END >= RANGE2.START AND RANGE2.END >= RANGE1.START

    Not wrong

    CoyneTheDup:
    Well, first of all, it does require that the ranges be "normal", such that RANGE.START <= RANGE.END, always. Then, start with a WHERE clause that proves two ranges don't overlap:

    WHERE RANGE1.END < RANGE2.START OR RANGE2.END < RANGE1.START

    whjich is what I quoted:

    Yamikuronue:
    WHERE RANGE1.END < RANGE2.START OR RANGE2.END < RANGE1.START

    OH! Reading comprehension fail on my part. That test is to prove they do NOT overlap.

  • (disco) in reply to Yamikuronue
    Yamikuronue:
    OH! Reading comprehension fail on my part. That test is to prove they do NOT overlap.

    Yes, I got confused by my own lack of close reading, and didn't realize that you'd quoted the "non overlap test." And then I started typing...

  • (disco) in reply to boomzilla
    boomzilla:
    Yes, I got confused by my own lack of close reading, and didn't realize that you'd quoted the "non overlap test." And then I started typing...

    hmm.... ditto.

    whoopsies?

  • (disco)

    I started writing my own and damaged my brain something fierce. I think it's the association of GT/LT with earlier/later that my brain refuses to acknowledge. I'd have to do it on paper and I'm not putting that much work into a forum thread XD

    I can write test cases though:

    CASE 1: Range1: 9am-10am Range2: 11am-noon Expected output: NO OVERLAP

    CASE 2: Range1: 11am-noon Range2: 9am-10am Expected output: NO OVERLAP

    CASE 3: Range1: 9am-noon Range2: 10pm-11pm Expected output: NO OVERLAP

    CASE 4: Range1: 9am-noon Range2: 10am-11am Expected output: OVERLAP

    CASE 5: Range1: 9am-noon Range2: 10am-1pm Expected output: OVERLAP

    CASE 6: Range 1: 9am-noon Range2: 8am-10am Expected output: OVERLAP

  • (disco) in reply to Yamikuronue
    Yamikuronue:
    I started writing my own and damaged my brain something fierce 
    > "excuse me, boss"
< "yes, what is is smithers‽"
> "I cannot brain today. I have the dumb"
< "... I see..."
> "Can i go home early? The work with dumb not go well."
< "... How did you get into work today like that?"
> "I... I'm not sure..."
  • (disco)

    Trying to figure out if this would work in T-SQL...

    @Range1Start BETWEEN @Range2Start AND @Range2End
   OR @Range1End BETWEEN @Range2Start AND @Range2End
  • (disco) in reply to chubertdev

    assuming SQL2012 and appropriate data types.... yes.

    unsure about older versions.

  • (disco) in reply to accalia
    accalia:
    assuming SQL2012 and appropriate data types.... yes.

    unsure about older versions.

    I usually develop with 2008.

  • (disco) in reply to chubertdev
    chubertdev:
    I usually develop with 2008.

    in that case it probably works with 2008 as well. i don't have that version in front of me to test and can't be arsed to look up the syntax on MSDN.

  • (disco) in reply to accalia
    accalia:
    should find overlaps
    Not all, though. Example: 7am - 10am 8am - 11am
  • (disco) in reply to aliceif
    aliceif:
    Not all, though. Example: 7am - 10am 8am - 11am

    hmm... let's see.....

    0700 <= 0800 && 1000 >= 1100 ==> FALSE 0800 <= 0700 && 1100 >= 1000 ==> FALSE

    huh... you're right. my solution catches overlapped appts but not an appointment completely contained by another appt.

Leave a comment on “Is Something Happening Right Now?”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article