Calculating Total Time Duration in MySQL

Calculating total time duration in MySQL

Try converting to seconds, summing, then converting back to a time:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Duration)))
FROM Table1

Result:

17:33:00

Calculate the total time duration on last row in mysql

I'm not super proud of this answer, but it should work:

SELECT 0 as is_total, CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration 
    from user u, user_group ug, (
    select *, (
        select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1
    ) stop_id from event L1
) start join event end on end.event_id=start.stop_id
where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid  and ug.group_id = start.group_id 

UNION
SELECT 1, null, null, null, sum(duration)
FROM
(
    SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration 
        from user u, user_group ug, (
        select *, (
            select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1
        ) stop_id from event L1
    ) start join event end on end.event_id=start.stop_id
    where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid  and ug.group_id = start.group_id 

) total
ORDER BY is_total

Mysql SUM two time and return total duration as days, hours, minutes

You can use the following query to get the desired result:

select CONCAT(FLOOR(time_in_sec/(60*60*24))," days " ,
FLOOR((time_in_sec/(60*60*24) - FLOOR(time_in_sec/(60*60*24)))*24)," hours ",
FLOOR(((time_in_sec/(60*60*24) - FLOOR(time_in_sec/(60*60*24)))*24 - 
FLOOR((time_in_sec/(60*60*24) - 
FLOOR(time_in_sec/(60*60*24)))*24))*60)," minutes" ) total_duration

FROM (
select SUM(TIME_TO_SEC(r.duration)) time_in_sec 
from routes r LEFT JOIN drivers d ON d.id = r.driver_id 
GROUP BY r.driver_id') T;

The logic of the query is as follows:

1. Sum total seconds and store it in variable time_in_sec. Use this result in from:

FROM (
    select SUM(TIME_TO_SEC(r.duration)) time_in_sec 
    from routes r LEFT JOIN drivers d ON d.id = r.driver_id 
    GROUP BY r.driver_id') T;

2. Rest is pure mathematics.
   
   a.)For days: Floor(time_in_sec/3600).
   
   b.)For Hours: Floor((time_in_sec/3600 - days)*24)
   
   c.)For minutes: Floor(((time_in_sec/3600 - days)*24 - hours)*60)

Calculate Total Time Value in Hours & Minutes

Use TIME_TO_SEC to convert to seconds, then SUM. For output you could use SEC_TO_TIME function to get hours:mins. E.g:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(wp_frm_item_metas.meta_value))) AS total_hours
   FROM wp_frm_item_metas
WHERE 1=1 
   AND wp_frm_item_metas.field_id = '103'

I haven't tested the above but should get you close...

calculating total time using timestamp

Okay, there are multiple possible answers.

If you are sure, that you have an entry for every second in your table, you can simply do this:

select
id, `binary`,
sec_to_time(count(distinct `timestamp`))
from
t
where `timestamp` between concat(curdate(), ' 00:00:00') and concat(curdate(), ' 23:59:59')
/*you could also do 
  where date(timestamp) = curdate()
but having a function on a column does not allow MySQL to use an index*/
group by id, `binary`

Please also note, that it's not a good idea to use reserved keywords as column names, like it's the case here with binary and timestamp. Not only are they scarcely descriptive, but you also always have the trouble to have to use backticks

With distinct you get only unique entries in that column. With count you count the seconds and with sec_to_time you transform it into a better readable format.

You can pimp this by putting an unique index over the columns (id, binary, timestamp) and instead of insert ... do insert ignore .... This way you would get only one entry per second in the table.

If you can't assume, that you have an entry for every second, then it gets more complicated. Best would be have an additional column, that indicates a value change in column binary. You can simulate it with a variable like in the following example, but it might not have good performance.

SELECT `binary`, SEC_TO_TIME(SUM(secondsOnOffPerGroup))
FROM (
    SELECT
    id, `binary`, valueChangeGroup, TIMESTAMPDIFF(SECOND, MIN(`timestamp`), MAX(`timestamp`)) + 1 as secondsOnOffPerGroup
    FROM (
        SELECT
        t.*,
        @valueChangeGroup := IF(@prevB != `binary`, @valueChangeGroup + 1, @valueChangeGroup) as valueChangeGroup,
        @prevB := `binary`
        FROM
        t
        , (SELECT @prevB := null, @valueChangeGroup := 0) var_init_subquery_alias
        WHERE 
        `timestamp` between concat(curdate(), ' 00:00:00') and concat(curdate(), ' 23:59:59')
        ORDER BY id, `timestamp`
    ) sq
    GROUP BY id, `binary`, valueChangeGroup
) sq2
GROUP BY `binary`

What we do here, is to order by id and timestamp first. Then we increment a variable if the value of the current row differs from the previous row. In the outer query we group by this variable and get the min and max timestamp for each group, so we can calculate the difference. I'm adding + 1 there, because when the door is open or closed for just one second, the difference is obviously 0. If it's open or closed 2 seconds, the difference is 1. In the outermost query we group by binary and sum the seconds.

• see it working live in an sqlfiddle

How to calculate total time between datetime stamps in MySQL

You can use the Unixtime functions in MySQL to do your calculations.

mysql> SELECT UNIX_TIMESTAMP('2015-08-24 10:10:53') - UNIX_TIMESTAMP('2015-08-23 10:05:53');
+-------------------------------------------------------------------------------+
| UNIX_TIMESTAMP('2015-08-24 10:10:53') - UNIX_TIMESTAMP('2015-08-23 10:05:53') |
+-------------------------------------------------------------------------------+
|                                                                         86700 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You could use MIN() and MAX() maybe to get the earliest and latest dates?

mysql> SELECT UNIX_TIMESTAMP(MAX(datestamp)) - UNIX_TIMESTAMP(MIN(datestamp)) FROM table;
+-------------------------------------------------------------------------------+
| UNIX_TIMESTAMP(MAX(datestamp)) - UNIX_TIMESTAMP(MIN(datestamp)) |
+-------------------------------------------------------------------------------+
|                                                                         86700 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Are you trying to calculate the difference between all of them or only those who are next to each other in the list?

I'll update this answer with more info if requ'd.

EDIT

Updated with conditional.

SELECT UNIX_TIMESTAMP(MAX(datestamp)) - UNIX_TIMESTAMP(MIN(datestamp)) FROM task_details WHERE task_type = 2 GROUP BY task_id ORDER BY datestamp;

Try this out. The dummy data above didn't have many rows. I tested briefly. Please report back.

EDIT #2

I just added some additional rows to the test table I have and the query worked well.

Note that 'datetime' is a reserved word in MySQL and you should consider renaming that column.

Is there a way to calculate total breaks time in between first_check_in and last_check_out?

In your original problem, following approach can work:

SELECT   dt.employee_id,
         Min(dt.check_in) AS check_in,
         CASE WHEN COUNT(dt.check_out) = COUNT(*) THEN MAX(dt.check_out)
              ELSE NULL
         END AS check_out,
         Sum(dt.break_interval) AS break_interval
FROM     (
                  SELECT   employee_id,
                           check_in,
                           check_out,
                           timestampdiff(minute, Lag(check_out) over w, check_in) AS break_interval
                  FROM     attendance_employees
                  WHERE    check_in BETWEEN '2019-09-15 00:00:00' AND      '2019-09-16 23:59:59'
                  WINDOW w AS (partition BY employee_id, date(check_in) ORDER BY check_in ASC) ) dt
GROUP BY dt.employee_id,
         date(dt.check_in);

| employee_id | check_in            | check_out           | break_interval |
| ----------- | ------------------- | ------------------- | -------------- |
| 2095        | 2019-09-16 08:30:00 | 2019-09-16 18:30:00 |                |
| 2103        | 2019-09-15 07:30:00 |                     | 75             |
| 2103        | 2019-09-16 08:00:00 | 2019-09-16 18:00:00 | 60             |

View on DB Fiddle

Details:

• In MySQL 8+, we can use Analytic/Window functions like LAG(), to compute the just previous check_out time for an employee on the same date. To get this, our partition window will be over employee, and the check_in date. Partition window will be sorted by check_in time, so that the LAG() returns the just previous entries only.
• We can determine the break_interval in minutes, using TimeStampDiff() function between the current row's check_in time and previous row's check_out time.
• Once we have determined these values for every row; we can utilize them in a Subquery (Derived Table) and do sum aggregation of break_interval for an employee on a specific date. Also, getting check_out time will require some special handling when there is no check_out in the last entry of an employee for a specific date, as you need NULL there and MAX(..) won't return NULL.
• You can also make the query sargeable by avoiding DATE() function on the check_in time and use Range conditions.

How to calculate total time of multiple rows with different date and time columns in table using Laravel and mysql

SELECT SUM(TIMESTAMPDIFF(SECOND, TIMESTAMP(start_date, start_time), TIMESTAMP(end_date, end_time)),
       ...
FROM ...
WHERE ...
GROUP BY ...

---

Related Topics