ic205 ic205 - 1 month ago 6
MySQL Question

mysql return TIMEDIFF between row in single table

in my app i a have an appointments that the users orders, and the table looks like that:

id simTypeID simID simDateTime startHour endHour
1 100 1 2016-09-01 09:00:00 09:00:00 10:00:00
2 100 2 2016-09-01 10:00:00 10:00:00 11:00:00
3 100 3 2016-09-01 11:00:00 11:00:00 12:00:00
4 100 1 2016-10-11 10:00:00 10:00:00 11:00:00


*note: start time is 09:00 AM everyday

now i want to get all the free time in specific date (for example 2016-09-01) by simID

it means that the result need to be in tbl_free_time:

simID startFreeHour endFreeHour
1 10:00:00 23:59:00
2 09:00:00 09:59:00
2 11:00:00 23:59:00
3 09:00:00 10:59:00
3 12:00:00 23:59:00


how can I use TIMEDIFF on the same table ? ( need to take endHour of first row and startHour of second row ).

any other way to solve this problem will be appreciated!

i was thinking to create temp tbl with:

CREATE TABLE tbl_sim_temp LIKE tbl_sim

INSERT tbl_sim_temp
SELECT * FROM tbl_sim
WHERE simDateTime BETWEEN '2016-09-01 00:00:00' AND '2016-09-01 23:59:59'
ORDER BY id ASC


and than to use LEFT JOIN , something like that-

SELECT TIMEDIFF(a.startHour,b.endHour)
FROM a tbl_sim
LEFT JOIN b tbl_sim_temp
WHERE a.simID = b.simID AND a.simTypeid = b.simtypeid AND a.id = b.id+1


but I'm stuck

Answer

Here is my answer.

SELECT simID, startFreeHour, endFreeHour
FROM (
    SELECT simID, "09:00:00" as startFreeHour, SUBTIME(startHour, '00:01:00') as endFreeHour
    FROM tbl_sim 
    WHERE 
        startHour > "09:00:00"
        AND DATE(simDateTime) = "2016-09-01" 

    UNION 

    SELECT simID, endHour as startFreeHour, "23:59:00" as endFreeHour
    FROM tbl_sim 
    WHERE 
        endHour < "23:59:00"
        AND DATE(simDateTime) = "2016-09-01"
) as t1
ORDER BY simID ASC, startFreeHour ASC

It only works if there is only one row for day and simId and assuming that seconds will always be zero to limit to 23:59:00. So with this scenario will not work:

id simTypeID simID   simDateTime            startHour endHour  
1  100       1       2016-09-01 09:00:00  10:00:00  11:00:00 
1  100       1       2016-09-01 09:00:00  12:00:00  13:00:00 

If this scenario is possible just tell me and I will try it again.

Comments