Count hours between times

Question

I asked a similar question a few days ago but didn't quite get an answer for what I'm looking for, specifically with SQL (the question is Check if time in a database surpasses specific hour).

Basically, I've got the following query:

    SELECT DAYNAME(arrivalTime) AS day, COUNT(*) AS count
    FROM bookings
    WHERE HOUR(arrivalTime) = 13
    AND DAYNAME(arrivalTime) = 'Monday'

This will count the number of entries there are for bookings that start at 13:00 on a Monday. How do I change this so that each hour from the arrival time to the pick up time is counted?

For example, if I had the arrivalTime - pickupTime as 13:00 - 17:00 then 13:00, 14:00, 15:00 and 16:00 would all count this as an entry.

Had a couple of suggestions but didn't really manage to get far with them.

Edit:

The calendar I'm making:

My database:

Samples and expected outputs:

arrivalTime - pickupTime 

13 - 15 (hours 13, 14 are counted)

10 - 14 (hours 10, 11, 12, 13 are counted)

16 - 17 (hours 16 are counted)

13 - 17 (hours 13, 14, 15, 16 are counted)

11 - 12 (hours 11 are counted)

Basically, right now it's simply counting the number of entries that have arrivalTime as X. So if we have 10 entries that start at 13:00 on a Monday, on my HTML table that I linked it will have the number 10 next to 13:00. I want to make it that each hour is counting how many bookings actually go over then.

To make it easier to visualise, this is simply put a dog kennel. We're checking to see how many dogs are in the kennel at each hour. Thus, if there's a booking from 13:00 - 15:00 it shouldn't just add a +1 to 13:00 but also to 14:00 as the time overlaps. Maybe a little bit more complex than I thought it'd be.

Edit 2:

Right now I've got this query in my function

SELECT DAYNAME(arrivalTime) AS day, COUNT(*) AS count
FROM bookings
WHERE HOUR(arrivalTime) = $hour
AND DAYNAME(arrivalTime) = '$dayname'

In HTML I pass the values like so

<td>
    <span>
        <p style="float:left">10:00</p>
        <p style="float:right">&nbsp;&nbsp;&nbsp;(<?php $var->get_CalendarCount($conn,10,'Monday'); ?>)</p>
    </span>
</td>

Answer 1

You mean like this?

SELECT DAYNAME(arrivalTime) AS day, COUNT(arrivalTime) AS count
  FROM bookings
  WHERE TIME(arrivalTime) BETWEEN '13:00:00' AND '17:00:00'
  AND DAYNAME(arrivalTime) = 'Monday'

Or if you need only 13, 14, 15, 16, 17 then:

SELECT DAYNAME(arrivalTime) AS day, COUNT(arrivalTime) AS count
  FROM bookings
  WHERE (TIME(arrivalTime) BETWEEN '13:00:00' AND '17:00:00') 
  AND MINUTE(TIME(arrivalTime)) = 0 
  AND SECOND(TIME(arrivalTime)) = 0
  AND DAYNAME(arrivalTime) = 'Monday'

// EDIT to answer comment

So you need to count bookings between (example) 13:00 - 17:00 from periods 13:00 - 13.59, 14.:00 - 14:59 etc?

SELECT DAYNAME(arrivalTime) AS day, COUNT(arrivalTime) AS count, CONCAT(HOUR(arrivalTime), ':00 - ', HOUR(arrivalTime) + 1, ':00') as Hours
  FROM bookings
  WHERE (HOUR(arrivalTime) BETWEEN '13' AND '17')
  AND DAYNAME(arrivalTime) = 'Monday'
  GROUP BY HOUR(arrivalTime);

// EDIT 2

Unfortunately I don't have time now to fix the query I wrote, but it would be easier to use a function instead of single select (make select for each one hour period).

But there are two things:

1. If you want it for each hour from ex: 10:00 - 17:00: (ex: 10:00 - 10:59), it would be all records with pickupTime OR arrivalTime between 10:00 AND 10:59
2. if you want then total from this period ex 10:00 - 17:00, it won't be a sum, cuz there will be duplicates, ex: 10:00 - 10:59 may contain same records as 11:00 - 11:59.

// EDIT 3

Check if this function will satisfy your needs, when you call it for any cell of your calendar. If you want to return x instead of zero, simply put if and return char. It should work for 10:00 - 10:59 (one hour periods) or 10:00 - 16:59 (total period)

CREATE FUNCTION fn_getCount(p_dayName VARCHAR(10), p_fromHour INT, p_toHour INT) 
  RETURNS INT
BEGIN
  DECLARE v_count INT;

  SELECT COUNT(b.arrivalTime) INTO v_count
    FROM bookings b
    WHERE 
      ((HOUR(b.arrivalTime) BETWEEN p_fromHour AND p_toHour - 1) OR
      (HOUR(b.pickupTime) BETWEEN p_fromHour AND p_toHour - 1) OR
      (HOUR(b.arrivalTime) < p_fromHour AND HOUR(b.pickupTime) > p_toHour - 1)) AND
      DAYNAME(b.arrivalTime) = p_dayName;

  RETURN v_count;
END;

Call it like this:

SELECT fn_getCount('Saturday', 14, 17);

I don't know php, I know .NET, but if you want a whole table returned by the server, you can simply make a stored procedure which call this function wherever needed and call the procedure from PHP. I am not an expert but if you want whole calendar returned by the Server, you would have to create pivot table

// EDIT 4 (30-11-2015 00:04)

Here you have Function for single cell and stored procedure which generates entire calendar, I have added function to evaluate first day of a week for supplied date, because you need to provide date as well cuz otherwise it will return all Mondays, not only the one from the week you are interested in.

Function to evaluate single cell:

DELIMITER $$
DROP FUNCTION IF EXISTS fn_getCount;
CREATE FUNCTION fn_getCount(p_weekDate DATE, p_dayName VARCHAR(10), p_fromHour INT, p_toHour INT) 
  RETURNS INT
BEGIN
  DECLARE v_count INT;

  SELECT COUNT(b.arrivalTime) INTO v_count
    FROM bookings b
    WHERE 
      ((HOUR(b.arrivalTime) BETWEEN p_fromHour AND p_toHour - 1) OR
      (HOUR(b.pickupTime) BETWEEN p_fromHour AND p_toHour - 1) OR
      (HOUR(b.arrivalTime) < p_fromHour AND HOUR(b.pickupTime) > p_toHour - 1)) AND
      DAYNAME(b.arrivalTime) = p_dayName AND 
      (DATE(b.arrivalTime) BETWEEN fn_firstDayOfWeek(p_weekDate) AND DATE_ADD(fn_firstDayOfWeek(p_weekDate), INTERVAL 6 DAY));

  RETURN v_count;
END$$
DELIMITER ; 

Function to return date of Monday for the week from which we provided a date as parameter.

DELIMITER $$
DROP FUNCTION IF EXISTS fn_firstDayOfWeek;
CREATE FUNCTION fn_firstDayOfWeek(day DATE)
  RETURNS DATE
BEGIN
  RETURN SUBDATE(day, WEEKDAY(day));
END$$
DELIMITER ; 

Procedure to generate entire calendar as pivot table:

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_GetCalendar;
CREATE PROCEDURE sp_GetCalendar(
  p_weekDate DATE,
  p_fromHour INT, 
  p_toHour INT
)
BEGIN
  DECLARE v_currHour INT;
  DROP TEMPORARY TABLE IF EXISTS Calendar;
  CREATE TEMPORARY TABLE Calendar
  (
    Hours VARCHAR(30),
    Monday VARCHAR(30),
    Tuesday VARCHAR(30),
    Wednesday VARCHAR(30),
    Thursday VARCHAR(30),
    Friday VARCHAR(30),
    Saturday VARCHAR(30),
    Sunday VARCHAR(30)
  );

  SET v_currHour = p_fromHour;
  START TRANSACTION;
  WHILE (v_currHour < p_toHour) DO
    INSERT INTO Calendar (Hours, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
      VALUES (
        CONCAT(v_currHour, ':00 - ', v_currHour + 1, ':00'), 
        CASE WHEN fn_getCount(p_weekDate, 'Monday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Monday', v_currHour, v_currHour + 1) END, 
        CASE WHEN fn_getCount(p_weekDate, 'Tuesday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Tuesday', v_currHour, v_currHour + 1) END, 
        CASE WHEN fn_getCount(p_weekDate, 'Wednesday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Wednesday', v_currHour, v_currHour + 1) END, 
        CASE WHEN fn_getCount(p_weekDate, 'Thursday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Thursday', v_currHour, v_currHour + 1) END, 
        CASE WHEN fn_getCount(p_weekDate, 'Friday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Friday', v_currHour, v_currHour + 1) END, 
        CASE WHEN fn_getCount(p_weekDate, 'Saturday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Saturday', v_currHour, v_currHour + 1) END, 
        CASE WHEN fn_getCount(p_weekDate, 'Sunday', v_currHour, v_currHour + 1) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Sunday', v_currHour, v_currHour + 1) END);
    SET v_currHour = v_currHour + 1;
  END WHILE;
  INSERT INTO Calendar (Hours, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
    VALUES (
      CONCAT('Total: ', p_fromHour, ':00 - ', p_toHour, ':00'), 
      CASE WHEN fn_getCount(p_weekDate, 'Monday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Monday', p_fromHour, p_toHour) END,  
      CASE WHEN fn_getCount(p_weekDate, 'Tuesday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Tuesday', p_fromHour, p_toHour) END, 
      CASE WHEN fn_getCount(p_weekDate, 'Wednesday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Wednesday', p_fromHour, p_toHour) END, 
      CASE WHEN fn_getCount(p_weekDate, 'Thursday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Thursday', p_fromHour, p_toHour) END, 
      CASE WHEN fn_getCount(p_weekDate, 'Friday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Friday', p_fromHour, p_toHour) END, 
      CASE WHEN fn_getCount(p_weekDate, 'Saturday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Saturday', p_fromHour, p_toHour) END, 
      CASE WHEN fn_getCount(p_weekDate, 'Sunday', p_fromHour, p_toHour) = 0 THEN 'x' ELSE fn_getCount(p_weekDate, 'Sunday', p_fromHour, p_toHour) END);
  COMMIT;

  SELECT * FROM Calendar;
  DROP TEMPORARY TABLE IF EXISTS Calendar;
END$$
DELIMITER ; 

Call them for testing like this:

CALL sp_GetCalendar('2015-11-07', 10, 17);

SELECT fn_getCount('2015-11-07', 'Saturday', 10, 17);

SELECT fn_firstDayOfWeek('2015-11-07');

Output:

Code might be a little bit redundant, but I am really short on time.

Answer 2

I guess you need to count even there is no booking in a particular Hour. You need a Hour's table to do this.

SELECT H.day_name AS day, 
       Count(b.arrivaltime) AS count 
FROM   hour_table H 
       LEFT OUTER JOIN bookings B 
                    ON h.day_name = Dayname(arrivaltime) 
                       AND h.hours = Hour(arrivaltime) 
WHERE  h.hours BETWEEN 13 AND 17 
       AND h.day_name = 'Monday' 
GROUP  BY H.day_name

Edit : If you don't want to count every booking between X and Y. Then you need conditional Aggregate. Something like this

Count(case when h.hours BETWEEN 13 AND 17 then b.arrivaltime .. end) AS count 

Can add full answer when there is enough information in question