Search code examples
mysqldelay

How to get total delay from two columns group by day?

I have a table in a MySql database named as internet with 3 columns: id, dropped_at and dropped_to as shown in this image

enter image description here

How to find total delay group by date?

Well, I have tried but it's not working properly here is my MySql code:

SELECT
    dropped_at,
    dropped_to,
    TIMEDIFF(dropped_to,dropped_at) AS delay
FROM
    internet
WHERE
    WEEKDAY(dropped_at) BETWEEN 0 AND 6 AND 
  WEEK (dropped_at) = WEEK (NOW())
GROUP BY CAST(dropped_to AS DATE)
Solution

  • If you want total delay for each day then the following query would work:

    SELECT 
 DATE(dropped_at) AS date,
 SUM(TIMESTAMPDIFF(MINUTE,dropped_at,dropped_to)) AS delayInMinutes
FROM internet
GROUP BY date 
ORDER BY date;

    Note: The delay is in MINUTES. You can change it to any unit as you like

    If you want the delay in hh:mm:ss format then try the following query instead

    SELECT 
 DATE(dropped_at) AS date,
 SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,dropped_at,dropped_to))) AS delay
FROM internet
GROUP BY date 
ORDER BY date;