0

i want to get the time conflict in a specific day

my table

| starttime | endtime  | day  | subject |
------------------------------------------
| 09:00:00  | 11:00:00 |  M   |   math  |
 -----------------------------------------
| 09:00:00  | 11:00:00 |  M   | science |
 -----------------------------------------
| 06:00:00  | 09:00:00 |  M   | History |
 ----------------------------------------- 
| 06:00:00  | 09:00:00 |  T   |   P.E.  |
 -----------------------------------------
| 10:00:00  | 12:00:00 |  M   | English |
 -----------------------------------------

the output i want to get

| starttime | endtime  | day  | subject |
------------------------------------------
| 09:00:00  | 11:00:00 |  M   |   math  |
 -----------------------------------------
| 09:00:00  | 11:00:00 |  M   | science |
 -----------------------------------------
| 10:00:00  | 12:00:00 |  M   | English |
 -----------------------------------------
christian farrec
  • 23
  • 2
  • 2
    Please define "time conflict". Also show the results you want. – Gordon Linoff Sep 13 '17 at 11:45
  • Also, I don't think it's probably a good idea to just be storing time in your table. Instead, store datetime (date + time) which lends the ability to handle dates as well as time. – Tim Biegeleisen Sep 13 '17 at 11:46
  • The ANSI SQL data type _timestamp_ includes both a date part and a time part. – jarlh Sep 13 '17 at 11:47
  • Does this answer your question? [What is a simple and efficient way to find rows with time-interval overlaps in SQL?](https://stackoverflow.com/questions/117962/what-is-a-simple-and-efficient-way-to-find-rows-with-time-interval-overlaps-in-s) – Divakar Dec 01 '22 at 06:11

1 Answers1

1

The only interpretation of your question which makes any sense to me is that you want to identify records whose time range overlaps ("conflicts") with one or more other time ranges. If so, we can do a self join on the condition of overlap, and then retain those ranges which do have an overlap.

SELECT DISTINCT
    t1.starttime,
    t1.endtime,
    t1.day,
    t1.subject
FROM yourTable t1
LEFT JOIN yourTable t2
    ON
        (t1.starttime > t2.starttime AND t1.starttime < t2.endtime AND t1.day = t2.day) OR
        (t1.endtime > t2.starttime   AND t1.endtime < t2.endtime AND t1.day = t2.day) OR
        (t1.starttime = t2.starttime AND t1.endtime = t2.endtime AND t1.day = t2.day AND
         t1.subject <> t2.subject)
WHERE
    t2.starttime IS NOT NULL;

Output:

    starttime   endtime day subject
1   07:00:00    10:00:00    T   History
2   07:00:00    10:00:00    T   P.E.

Demo here:

Rextester

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • if the time is 7:00:00 - 9:00:00, day is Monday, subject is math and another row is 7:00:00 - 9:00:00, day is Monday, subject is science. it's not returning. – christian farrec Sep 13 '17 at 13:27
  • 1
    You've changed your question (yet again; hence the downvotes). Let me update my answer. – Tim Biegeleisen Sep 13 '17 at 13:29
  • sorry for that tim. help pls – christian farrec Sep 13 '17 at 15:31
  • @christianfarrec I have answered your updated question. Did you even bother to check? – Tim Biegeleisen Sep 13 '17 at 15:40
  • DROP TABLE IF EXISTS yourTable; CREATE TABLE yourTable (starttime time, endtime time, day varchar(55), subject varchar(55)); INSERT INTO yourTable (starttime, endtime, day, subject) VALUES ('10:00:00', '11:00:00', 'T', 'math'), ('11:00:00', '14:00:00', 'T', 'science'), ('07:00:00', '10:00:00', 'T', 'History'), ('07:00:00', '10:00:00', 'T', 'P.E.'); Try this table it doesn't return the time conflict – christian farrec Sep 13 '17 at 15:51
  • @christianfarrec I updated my query to take your edge cases into account. – Tim Biegeleisen Sep 13 '17 at 16:13