SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Logic MySQL Based Problem

    All,

    Am having a bit of a problem trying to work something out.

    Imagine there is a table like so TIMESPAN(ID, start, end) where ID is an auto incrementing integer (which is irrelevant), start and date are MySQL date field types.

    TIMESPAN can contain one or more records.

    Now, imagine a user wants to come along and add another TIMESPAN with a start and end date. I need a way of making sure that these date sets do not overlap (so, the start date cannot be inbetween the start and end of a singleTIMESPAN in the database, the end date cannot be between the start and end date of a single TIMESPAN, so both start and end could fit in the middle of the end of one and the beginning of another, but one cannot start inbetween two, and finish during the second etc.

    I've been trying for a while to think up a QUERY that will do either of the following: return 0 records, as the date is OK or return 1 or more records as the date is OK (returning 1 or more or 0 respectively if it is not OK), to determine in PHP whether the next can be valid.

    Could anybody help me out of my problem?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you please just confirm whether it's okay for the new row to have a span that fits inside of an existing row's span?

    have a look at the following diagram --
    Code:
                  proposed        proposed
                    start             end
                      |               |
    1    start---end  |               |
                      |               |
    2          start--|--end          |
                      |               |
    3                 |  start---end  |
                      |               |
    4         start---|---------------|---end
                      |               |
    5                 |        start--|--end
                      |               |
    6                 |               |  start---end
    you're saying you want to prevent the proposed new row only in cases 2, 4, and 5, but ~not~ case 3?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you need to test for

    1) when newstart >= start and newstart <= end

    2) when newend >= start and newend <= end

    If either returns 1 or more rows then your new dates overlap with at least 1 set of existing start, end dates

  4. #4
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, the spans must be totally independent, they can't fit inside of each other nor follow any of the pervious test cases. However, assuming start = 2010-10-15 and end = 2010-10-25 of record 1, and the start of record 2 = 2010-11-05. Record 3 could be 2010-10-26 start and 2010-11-04 end but NOT start 2010-10-16 and end 2010-10-23.

    I will try Kalon's solution later, although managed to sort out my own that was much more complicated. Will that later and post my own solution later as do not have access to the file at the moment.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, so you're confirming that in the diagram above, the only legitimate proposed new timespans you would accept are cases 1 and 6, as cases 2 through 5 all have some sort of overlap which is not allowed

    so here's how you do it

    run this query --
    Code:
    SELECT 1
      FROM timespan
     WHERE end > proposedstart  /* eliminates case 1 */
       AND start < proposedend  /* eliminates case 6 */
    the purpose of this query is to find an overlap

    if it returns anything, you cannot accept the proposed new timespan

    if it returns no rows, that means your proposed new timespan is okay

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adamcoppard View Post
    I will try Kalon's solution later,
    forget what I suggested. r937's suggestion is better.

    1) it is only 1 query

    2) my suggestion doesn't take case 3 into account.

  7. #7
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, thank you very much!

    It works as intended and I am sure I tried that combination last night! Just goes to show late night coding doesn't point out the bleeding obvious! Thank you for your suggestions, and as an addendum as my code I produced last night compared everything eight times to get an answer, and required pulling a list of all the terms to begin with I can say that it's safely been solved.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •