Jump to content

time calculation


ramiwahdan

Recommended Posts

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.

screen.png

Edited by ramiwahdan
typo error
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

correct.png

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 by ramiwahdan
added more info for clarifications
Link to comment
Share on other sites

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 by ramiwahdan
clarification
Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.