Finding free blocks of time in mysql and php?

by

Try this query

SELECT
  a.id,
  a.start_hour,
  a.end_hour,
  TIMEDIFF(la.start_hour, a.end_hour) as `Free Time`
FROM appointment as a
  LEFT JOIN(SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la
    ON la.id = a.id + 1
  LEFT JOIN (SELECT * FROM appointment) AS ra ON a.id = ra.id

This will show these results

+---------------------------------------------+
¦ id ¦ start_hour BY ¦ end_hour   | Free Time |
¦----+---------------¦------------------------|
¦  1 ¦   08:00:00    ¦  08:15:00  | 05:45:00  |
¦  2 ¦   14:00:00    ¦  14:30:00  | 02:30:00  |
¦  3 ¦   17:00:00    ¦  17:45:00  | 03:15:00  |
¦  4 ¦   21:00:00    ¦  21:00:00  | (NULL)    |
+--------------------+------------------------+

Also you must have the 21:00:00 in the table or you wont be able to get the last time difference. i entered 21:00:00 as start and end date in the table.

EDITED

This is modified query

SELECT
  a.id,
  a.end_hour AS `Free time Start`,
  IFNULL(la.start_hour,a.end_hour) AS `Free Time End`,
  IFNULL(TIMEDIFF(la.start_hour, a.end_hour),'00:00:00') AS `Total Free Time`
FROM appointment AS a
  LEFT JOIN (SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la
    ON la.id = (SELECT MIN(id) FROM appointment where id > a.id LIMIT 1)

And the result is

+--------------------------------------------------------+
¦ id ¦ Free time Start ¦ Free Time End | Total Free Time |
¦----+-----------------¦---------------------------------|
¦  1 ¦   08:15:00      ¦ 14:00:00      |    05:45:00     |
¦  2 ¦   14:30:00      ¦ 17:00:00      |    02:30:00     |
¦  3 ¦   17:45:00      ¦ 21:00:00      |    03:15:00     |
¦  4 ¦   21:00:00      ¦ 21:00:00      |    00:00:00     |
+----------------------+---------------------------------+

The points to learn from this query are

  1. Timediff function usage. timediff(‘end time’,’start time’)
  2. Joining with upper number
  3. Avoid first record in join with a long offset and limit starting from 1 instead of zero
  4. IFNULL usage ifnull(‘if here comes null’,’select this then’)

More Related Contents:

  1. Getting time difference between two times in PHP [duplicate]
  2. Convert Time String to Decimal Hours PHP [closed]
  3. Comma separated numeric values – PHP CodeIgniter
  4. Using PHP data in MySQL [closed]
  5. Select those record only from table 1 where city exist against state id in table 2 in PHP
  6. PHP PDO prepared statements
  7. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  8. select count(*) from table of mysql in php
  9. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  10. Loading .sql files from within PHP
  11. Incorrect Integer (2147483647) is inserted into MySQL?
  12. How to loop through a mysql result set
  13. Cannot connect to MySQL 4.1+ using old authentication
  14. Object of class DateTime could not be converted to string
  15. PHP Commands Out of Sync error
  16. PHP MYSQL UPDATE if Exist or INSERT if not?
  17. MySQL/PHP Error:[2002] Only one usage of each socket address (protocol/network address/port) is normally permitted
  18. how to check and set max_allowed_packet mysql variable [duplicate]
  19. mysql_real_escape_string VS addslashes
  20. PHP: multiple SQL queries in one mysql_query statement
  21. MySql Error: 1364 Field ‘display_name’ doesn’t have default value
  22. how to re-format datetime string in php?
  23. Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
  24. Codeigniter Transactions
  25. PDOException “could not find driver” in php
  26. how to fetch all the row of the result in php mysql?
  27. Coverting Hex to Image in PHP?
  28. When should I close a database connection in PHP?
  29. php connection pooling mysql [duplicate]
  30. PHP/MySQL Pagination

Leave a Comment