Counts by eight-hour time interval - Mysql

Marco
March 23, 2023
273 views
3 votes
3 Answers

I’m using this query to count the records in the table and it works, I perform a calculation by time interval, every eight hours, but now I have the problem that I can’t calculate from 11.00pm until 7.00am the next day , I have to calculate three times, one from 07:00 to 15:00, one from 15:00 to 23:00 and one from 23:00 to 07:00 but this last one I can’t make it work. Of every day, month, year, I succeed but I miss this last step for the last block of time, can anyone help me to be able to calculate this too?

select 
    date_format(created_at, '%Y-%m-%d %H:00:00') as date_and_hour,
    count(*) as total_ticket_Id,
    case when count(*) >= 4 then 4 else count(*) end as up_ticket_Id,   
    case when count(*) > 4 then count(*) - 4 else 0 end as down_ticket_Id
from tickets
where created_at between '2022-01-01 07:00:00' and '2022-12-31 15:00:00'
    and cast(date_format(created_at, '%H') as SIGNED)  between 7 and 15
group by date_format(created_at, '%Y-%m-%d %H:00:00');

running the suggested query I get this result, but it is not divided by time groups as I should do, I have to count three separate groups, groups of 8 hours starting from 07:00 to 15:00, 15:00 to 23:00 and one from 11pm to 7am with that count of 4 every hour in one column and the rest in another column with a column of the total

date_and_hour   total_ticket_Id up_ticket_Id    down_ticket_Id
18/05/2022 00:00    50  4   46
19/05/2022 00:00    51  4   47
20/05/2022 00:00    7   4   3
20/05/2022 07:00    31  4   27
20/05/2022 15:00    17  4   13
21/05/2022 00:00    8   4   4
21/05/2022 07:00    14  4   10
21/05/2022 15:00    14  4   10
21/05/2022 23:00    6   4   2
22/05/2022 07:00    18  4   14
22/05/2022 15:00    14  4   10
23/05/2022 05:00    3   3   0
23/05/2022 07:00    38  4   34
23/05/2022 15:00    10  4   6
24/05/2022 01:00    2   2   0
24/05/2022 07:00    31  4   27
24/05/2022 15:00    23  4   19
24/05/2022 23:00    5   4   1
25/05/2022 07:00    29  4   25
25/05/2022 15:00    17  4   13
26/05/2022 01:00    3   3   0
26/05/2022 07:00    19  4   15
26/05/2022 15:00    12  4   8
27/05/2022 00:00    4   4   0
27/05/2022 07:00    27  4   23
27/05/2022 15:00    20  4   16
27/05/2022 23:00    7   4   3
28/05/2022 10:00    13  4   9
28/05/2022 15:00    11  4   7
28/05/2022 23:00    12  4   8
29/05/2022 08:00    19  4   15
29/05/2022 15:00    19  4   15
30/05/2022 03:00    6   4   2
30/05/2022 07:00    27  4   23
30/05/2022 15:00    15  4   11
30/05/2022 23:00    6   4   2
31/05/2022 07:00    26  4   22

if I export with this code it doesn’t give me the results

if I export with this code it doesn’t give me the results, I see them in the query but if I export in any format I don’t see anything and if I try to create a view it doesn’t create it

SELECT
  createdHour,
  hourGroup,
  COUNT(*) total_ticket_Id,
  LEAST(COUNT(*), 4) up_ticket_Id,
  IF(COUNT(*) > 4, COUNT(*) - 4, 0) down_ticket_Id
FROM (
  SELECT
    7 * (EXTRACT(HOUR FROM created_at) >= 7 AND EXTRACT(HOUR FROM created_at) < 15) +
    15 * (EXTRACT(HOUR FROM created_at) >= 15 AND EXTRACT(HOUR FROM created_at) < 23) +
    23 * (EXTRACT(HOUR FROM created_at) >= 23 OR EXTRACT(HOUR FROM created_at) < 7) AS hourGroup,
    CONCAT(DATE(created_at), ' ', LPAD((SELECT hourGroup), 2, '0'), ':00:00') createdHour
  FROM tickets
) groupedPerInterval
GROUP BY createdHour, hourGroup

<div class="alert alert-danger" role="alert"><h1>Errore</h1><p><strong>Query SQL:</strong>  <a href="#" class="copyQueryBtn" data-text="SELECT
  createdHour
  hourGroup
  COUNT(*) total_ticket_Id
  LEAST(COUNT(*)
  IF(COUNT(*) &gt
FROM (
  SELECT
    7 * (EXTRACT(HOUR FROM created_at) &gt
    15 * (EXTRACT(HOUR FROM created_at) &gt
    23 * (EXTRACT(HOUR FROM created_at) &gt
    CONCAT(DATE(created_at)
  FROM tickets
) groupedPerInterval
GROUP BY createdHour

Result csv:

<div class="alert alert-danger" role="alert"><h1>Error</h1><p><strong>SQL Query:</strong> <a href="#" class="copyQueryBtn" data -text="SELECT
   createdHour
   hourGroup
   COUNT(*) total_ticket_Id
   LEAST(COUNT(*)
   IF(COUNT(*) &gt
FROM (
   SELECT
     7 * (EXTRACT(HOUR FROM created_at) &gt
     15 * (EXTRACT(HOUR FROM created_at) &gt
     23 * (EXTRACT(HOUR FROM created_at) &gt
     CONCAT(DATE(created_at)
   FROM tickets
) groupedPerInterval
GROUP BY createdHour
<a href="./url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fselect.html" target="mysql_doc"><img src=" class="icon ic_b_edit">&nbsp
<p>
<code class="sql"><pre>
SELECT
   createdHour
   hourGroup
   COUNT(*) total_ticket_Id
   LEAST(COUNT(*)
   IF(COUNT(*) &gt
FROM (
   SELECT
     7 * (EXTRACT(HOUR FROM created_at) &gt
     15 * (EXTRACT(HOUR FROM created_at) &gt
     23 * (EXTRACT(HOUR FROM created_at) &gt
     CONCAT(DATE(created_at)
   FROM tickets
) groupedPerInterval
GROUP BY createdHour
</pre></code>
</p>
<p>
     <strong>Message from MySQL: </strong><a href="./url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fserver-error- reference.html" target="mysql_doc"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help"></a>
</p>
<code>#1046 - No database selected</code><br></div>

Tags: mysql

Answers

You can adapt the solutions in How to group datetime into intervals of 3 hours in mysql

The difference is that you need to special-case times before 07:00 to include them in the previous day.

SELECT
    CASE 
        WHEN HOUR(created_at) < 7 THEN DATE(DATE_SUB(created_at, INTERVAL 1 DAY))
        ELSE DATE(created_at)
    END AS date,
    CASE
        WHEN HOUR(created_at) < 7 OR HOUR(CREATED_AT) = 23 THEN '23-6'
        WHEN HOUR(created_at) < 15 THEN '8-14'
        ELSE '15-22'
    END AS hours,
    count(*) as total_ticket_Id,
    case when count(*) >= 4 then 4 else count(*) end as up_ticket_Id,   
    case when count(*) > 4 then count(*) - 4 else 0 end as down_ticket_Id
FROM tickets
GROUP BY date, hours

updated to calculate all 3 total columns and to show the date.

SELECT
  createdHour,
  hourGroup,
  COUNT(*) total_ticket_Id,
  LEAST(COUNT(*), 4) up_ticket_Id,
  IF(COUNT(*) > 4, COUNT(*) - 4, 0) down_ticket_Id
FROM (
  SELECT
    7 * (EXTRACT(HOUR FROM created_at) >= 7 AND EXTRACT(HOUR FROM created_at) < 15) +
    15 * (EXTRACT(HOUR FROM created_at) >= 15 AND EXTRACT(HOUR FROM created_at) < 23) +
    23 * (EXTRACT(HOUR FROM created_at) >= 23 OR EXTRACT(HOUR FROM created_at) < 7) AS hourGroup,
    CONCAT(DATE(created_at), ' ', LPAD((SELECT hourGroup), 2, '0'), ':00:00') createdHour
  FROM tickets
) groupedPerInterval
GROUP BY createdHour, hourGroup

Try this:

SELECT 
    date_format(MIN(created_at), '%Y-%m-%d %H:00:00') as date_and_hour,
    count(*) as total_ticket_Id,
    case when count(*) >= 4 then 4 else count(*) end as up_ticket_Id,   
    case when count(*) > 4 then count(*) - 4 else 0 end as down_ticket_Id
FROM tickets
WHERE created_at between '2022-01-01 07:00:00' and '2022-12-31 15:00:00'
GROUP BY (((DAYOFYEAR(created_at)-1)*24) + HOUR(created_at) - 7) >> 3;

Please signup or login to give your own answer.

Click here to cancel reply.

Counts by eight-hour time interval – Mysql

Answers