ramiwahdan Posted March 15, 2020 Share Posted March 15, 2020 (edited) Hi, I have an attendance system that calculates time in and time out for each day. I have a db field with time datatype that stores the total time for one day, say for example for today the total is 7:15:00 (7 hours and fifteen minutes). i am making a report to show all attendance records for a week. I need to show total of hours a week which is around 35-40 hours so how can this be done? screenshot of what i have. Edited March 15, 2020 by ramiwahdan typo error Quote Link to comment Share on other sites More sharing options...
gw1500se Posted March 15, 2020 Share Posted March 15, 2020 Start by changing that column to data type TIME. That stores it as hh:mm:ss. Then use: $totaltime=strtotime($time1)+strtotime($time2)+... ; echo gmdate("H:i:s",$totaltime); // If you want it back to hh:mm:ss or set whatever format you want. Quote Link to comment Share on other sites More sharing options...
ramiwahdan Posted March 15, 2020 Author Share Posted March 15, 2020 (edited) 42 minutes ago, gw1500se said: Start by changing that column to data type TIME. That stores it as hh:mm:ss. Then use: $totaltime=strtotime($time1)+strtotime($time2)+... ; echo gmdate("H:i:s",$totaltime); // If you want it back to hh:mm:ss or set whatever format you want. it is of datatype time and i am getting the total after looping through records but getting the total as full string like 130033 instead of 13:00:33, if i put your code i lose some time as it becomes 12:07:13 not sure why is that but my numbers are the correct one but need to put the ":" signs, any idea? Code used to get the correct time: $query2 = "select sum(Duration) as totals from attendance_records where OracleID = '".$userid."' and isdone= '".$isdone."' and DATE(ClockingOutDate) >= '".$sdate1."' and DATE(ClockingOutDate) <= '".$edate1."'"; $result2 = mysqli_query($con, $query2); while ($row2 = mysqli_fetch_assoc($result2)) { $totals= $row2['totals']; } ?> <td><td><td><td><td><h5>Total Attendance Time:</h5></td></td></td></td></td> <td><?php echo $totals ?></td> Edited March 15, 2020 by ramiwahdan added more info for clarifications Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 15, 2020 Share Posted March 15, 2020 As shown above, how about this: $totals= gmdate("H:i:s",$row2['totals']); Quote Link to comment Share on other sites More sharing options...
ramiwahdan Posted March 15, 2020 Author Share Posted March 15, 2020 (edited) 12 minutes ago, ginerjm said: $totals= gmdate("H:i:s",$row2['totals']); this function makes the answer wrong! after being correct. please help. i read about it it has to deal with GMT timing, i don't want time or date, this is a sum of times so i think my answer is number just like it shows in my screenshot 130033, i want to show that as 13:00:33 Edited March 15, 2020 by ramiwahdan clarification Quote Link to comment Share on other sites More sharing options...
Barand Posted March 15, 2020 Share Posted March 15, 2020 mysql> SELECT login_time, logout_time FROM login; +---------------------+---------------------+ | login_time | logout_time | +---------------------+---------------------+ | 2020-03-09 09:15:00 | 2020-03-09 15:00:00 | | 2020-03-10 09:30:00 | 2020-03-10 16:00:00 | | 2020-03-11 09:00:00 | 2020-03-11 13:30:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> SELECT sec_to_time(SUM(timestampdiff(SECOND, login_time, logout_time))) as total -> FROM login; +----------+ | total | +----------+ | 16:45:00 | +----------+ 1 Quote Link to comment Share on other sites More sharing options...
ramiwahdan Posted March 15, 2020 Author Share Posted March 15, 2020 12 minutes ago, Barand said: mysql> SELECT login_time, logout_time FROM login; +---------------------+---------------------+ | login_time | logout_time | +---------------------+---------------------+ | 2020-03-09 09:15:00 | 2020-03-09 15:00:00 | | 2020-03-10 09:30:00 | 2020-03-10 16:00:00 | | 2020-03-11 09:00:00 | 2020-03-11 13:30:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> SELECT sec_to_time(SUM(timestampdiff(SECOND, login_time, logout_time))) as total -> FROM login; +----------+ | total | +----------+ | 16:45:00 | +----------+ Thank you very very much Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.