logo
Tags down

shadow

how to get sum of the total time


By : Piotr Poprawski
Date : May 04 2020, 04:53 PM

Currently I'm work on with the attendance management system.i calculate how many hours work done in employees.i already calculate the how much hours working in day and it store in the mysql database.

$totaltime = (strtotime($time_out) - strtotime($time_in));

                $hours = sprintf('%02d', intval($totaltime / 3600));

                $seconds_remain = ($totaltime - ($hours * 3600)); 

                $minutes = sprintf('%02d', intval($seconds_remain / 60));   
                $seconds = sprintf('%02d' ,($seconds_remain - ($minutes * 60)));

                $final = '';

                if ($time_in == '' || $time_out == '')
                {
                    $final = '';
                }
                else
                {
                    $final .= $hours.':'.$minutes.':'.$seconds;
                }

for example

$time_in = 08:09:57
$time_out = 16:04:50
$final = 07:54:53 (total working hours)

now i try to get the current month total working time for each employee.how do get sum of the $final using php? sample data of the month_data

Emp_no  Date    Time_in Time_out    Total_hours TranID  
23  2019-08-01  07:54:40    16:01:40    08:07:00    1   
23  2019-08-02  07:42:35    16:02:53    08:20:18    2   

i want get the sum of the Total_hours for related one employee

Answer :

You can take a look at a query somewhat like this

SELECT SEC_TO_TIME(SUM(`Total_hours`) ) FROM `month_data` GROUP BY `Emp_no`;

there is a simple SUM function which can do this for you, it returns the total time in seconds though.

In order to turn that into readable time you can use the MySQL function SEC_TO_TIME.

edit

If the said column is not a TIME column you can CAST it to be handled as this type of column using CAST() the needed SQL would look something like

SELECT SEC_TO_TIME(SUM(CAST(`Total_hours` AS TIME)) ) FROM `month_data` GROUP BY `Emp_no`;

My suggestion would be to change the column type to TIME though.

edit 2

I was under the assumption that SUM() would be smart enough to convert the time to seconds and come up with the correct sum of the given times.

Not sure why yet but this is not the case, therefore you need to convert the given times to seconds first.

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`Total_hours`)) ) FROM `month_data` GROUP BY `Emp_no`;

Now I have not tested this but TIME_TO_SEC() seems to accept VARCHAR just fine so need to CAST() the column anymore.



Share : facebook icon twitter icon
Related Posts Related Posts :
  • clear array after form submit
  • pass url variable to javascript
  • access data using api
  • div position fixed css
  • add horizontal line between two div
  • disable toggle button css
  • css fixed line height
  • background image not working
  • table cell border
  • text line break css
  • overlay div on div
  • text direction rtl css
  • javascript convert string to an object
  • bind inner div click to outer div
  • jquery ajax oncomplete
  • how to use promise in for loop
  • jquery get element using variable
  • javascript send textbox value
  • pass div class content to another class or id
  • css new line after element
  • css calc not working properly
  • link disabled jquery
  • sql join table to subquery
  • MS Access SQL Issue with OR AND Operators
  • set textarea value using php
  • php merge two arrays into one
  • php form with google captcha
  • update current row in php
  • php date format mysql
  • mysql pdo select query
  • php function return an array
  • PHP mysqldump database to sql file
  • display image query
  • php create array using foreach loop
  • associative array php
  • group_concat in mysql laravel
  • php array_push empty
  • mysql on update cascade not working
  • pdo insert prepared statement
  • mysql insert ignore vs on duplicate key
  • mysql insert into existing row
  • mysql multi insert query
  • mysql group by show all rows
  • php change profile image
  • combine multiple arrays into one array php
  • how to sum values in an array
  • How to Create Facebook live stream without notification using API ?
  • on button click make div visible
  • css affect another element on hover
  • insert line after paragraph word
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org