Jump to content
#FlattenTheCurve ×
ramiwahdan

time calculation

Recommended Posts

Posted (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.

screen.png

Edited by ramiwahdan
typo error

Share this post


Link to post
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.

 

Share this post


Link to post
Share on other sites
Posted (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?

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

Share this post


Link to post
Share on other sites

As shown above, how about this:

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

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.