By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Adding together time hh:mm:ss to a total

P: 1
Hi,

I'm building a time report system for work and I want to show the total hours and minutes a project have taken but can't get it to work ...

I have a database (MySQL) in one table I have the date in one colum and the time in one colum. The date i formated like this YYYY-MM-DD and set to date. And the TIME is like this HH:mm:ss.
My code looks like this right now...

[PHP]<?php
$sql="select
p.id, p.date, p.time
from rap_time p
order by 1 desc";
$rows = mysql_query($sql);
$totaltime = 0;
while($row = mysql_fetch_array($rows)) {

echo"$row[1]";
echo", ";
echo"$row[2]";
echo"<br />";
$totaltime = $totaltime + $row[2];
}
echo"$totaltime";
?>
[/PHP]
The $totaltime just shows the total hours and it does not count the hours and minutes together.
So if I have an output of times that looks like this:
01:10:00
01:13:00
01:40:00
the totaltime will show:
3
Not 04:03:00
And yes I'm a noob on PHP and SQL...
May 14 '07 #1
Share this Question
Share on Google+
5 Replies


P: 3
You will have to write code that first counts the seconds and see if they overflow the 59 seconds limit. If they overflow, you add 1 to the minute calculation.

Next, you count the minutes and see if they overflow the 59 minutes limit. If they overflow, you add 1 to the hours calculation.
Note: don't forget to add the 1 from the previous calculation, if applicable.

Next, you count the hours and if applicable, add 1 from the previous calculation.
May 14 '07 #2

pbmods
Expert 5K+
P: 5,821
Here's how I would do it:

Assuming the times don't span multiple days, pick a random day. For example, '2007-05-14' (to pick a random one). Then prepend the date to your times and calculate their unix timestamps.

Then, all you have to do is add up the differences between your times and midnight of the same day:

Expand|Select|Wrap|Line Numbers
  1. var $date = '2007-05-14';
  2. var $datetime = strtotime("$date 00:00:00");
  3.  
  4. //    These are examples; you'll probably want to pull your numbers from the DB.
  5. var $times = array(
  6.     '01:15:45',
  7.     '03:11:02',
  8.     '10:15:45'
  9. );
  10.  
  11. var $sum = 0;
  12. foreach($times as $time) {
  13.     $sum += strtotime("$date $time") - $datetime;
  14. }
  15.  
  16. //    $sum holds the total number of seconds of all three times.
  17. print($sum);
  18.  
When the loop executes the first time, we are computing the difference between two datetimes:

Expand|Select|Wrap|Line Numbers
  1. $sum += strtotime('2007-05-14 01:15:45') - strtotime('2007-05-14 00:00:00');
  2.  
You're taking the number of seconds from January 1, 1979 (or something like that) until May 14, 2007 at 1:15 AM and 45 seconds. Then you take the number of seconds from January 1, 1979 until May 14, 2007 at midnight and subtract the two numbers.

Since May 14, 2007 01:15:45 occurred exactly 1 hour, 15 minutes and 45 seconds after May 14, 2007 00:00:00, $sum has just been +='ed to the number of seconds in 1 hour, 15 minutes and 45 seconds (4515).

Continue through the loop with the rest of your values, and you've got yourself a handy little time-summer.

Now the trick comes into play when your times span >1 day. But the fix for that isn't too difficult ;)
May 15 '07 #3

P: 1
Expand|Select|Wrap|Line Numbers
  1. list($h, $m, $s) = split('[:]', $time_1_here);
  2. list($h2, $m2, $s2) = split('[:]', $time_2_here);
  3.  
  4. $h = ($h * 60) * 60;
  5. $m = $m * 60;
  6. $h2 = ($h2 * 60) * 60;
  7. $m2 = $m2 * 60;
  8.  
  9.  
  10. $x = $h + $m + $s;
  11. $x2 = $h2 + $m2 + $s2;
  12.  
  13. $y = $x + $x2;
  14.  
  15. $y = $y / 60;
  16. $y = $y / 60;
  17.  
  18. echo $y;
[Please use CODE tags when posting source code. Thanks! --pbmods]
Jul 2 '07 #4

kovik
Expert 100+
P: 1,044
You know, the reason timestamps were ever used is because you can calculate the difference between them with simple subtraction. You can use mktime() to turn the dates into timestamps, and then do this:

Expand|Select|Wrap|Line Numbers
  1. $end - $start = $timeTaken;
  2. $years = date('Y', $timeTaken) - 1970; // because timestamps start at 1970
  3. $months = date('n', $timeTaken);
  4. $days = date('j', $timeTaken);
Jul 2 '07 #5

P: 9
Expand|Select|Wrap|Line Numbers
  1. <body>
  2. <input type="time" step="1" id="startTimeInput"  name="start_time" value=""><br>     <!--   write as like       02:31:55   -->
  3. <input  type="time" step="1" id="endTimeInput"  name="end_time" value=""><br>
  4. <button id="calcBtn">Seconds</button><br>
  5. <div id="result"></div>
  6. <script type="text/javascript" src="js/jquery.js"></script>
  7. <script type="text/javascript">
  8. document.getElementById('calcBtn').onclick = function(){
  9.     var startTime = document.getElementById('startTimeInput').value;
  10.     var endTime = document.getElementById('endTimeInput').value;
  11.   var result = getDeltaSeconds(startTime, endTime);
  12.   document.getElementById('result').innerHTML = result + ' sec';
  13. }
  14. function getDeltaSeconds(startTime, endTime){
  15.     var startTimeSeconds = getSeconds(startTime);
  16.   var endTimeSeconds = getSeconds(endTime);
  17.   return endTimeSeconds - startTimeSeconds;    
  18. }
  19. function getSeconds(timeString){
  20.     var parts = timeString.split(' ');
  21.   var time = parts[0];
  22.   var ampm = parts[1];
  23.   var timeParts = time.split(':');
  24.   var seconds = Number(timeParts[2]) + Number(timeParts[1]*60) + Number(timeParts[0]*60*60);
  25.   return ampm == 'AM' ? seconds : seconds + 12*60*60;
  26. }
  27. </script>
  28. </body>
  29.  
  30.  
Oct 4 '16 #6

Post your reply

Sign in to post your reply or Sign up for a free account.