time calculation

[ramiwahdan]

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

[gw1500se]

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.

 

[ramiwahdan]

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

[ginerjm]

As shown above, how about this:

$totals= gmdate("H:i:s",$row2['totals']);

[ramiwahdan]

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

[Barand]

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 |
+----------+

 

[ramiwahdan]

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