Jump to content


Photo

Calculation to find difference in minutes


  • Please log in to reply
5 replies to this topic

#1 dalton6275

dalton6275
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 April 2006 - 11:46 AM

Hey everyone,

Im trying to perform a date calculation to find the difference in minutes between 2 dates in MySQL.

currently i have:


<?php
while ($row=mysql_fetch_array($Recordset1))
{
$outagetime = $row_Recordset1['outage_end'] - $row_Recordset1['outage_start'];
echo number_format($outagetime, 3);
?>


But i keep getting the result 0.000. I tried adding more decimals but it doesnt seem to be working and iv checked the 2 variables and there is clearly 90 minutes difference but the system keeps reporting 0.000.


Im baffled. Any ideas




#2 litebearer

litebearer
  • Members
  • PipPipPip
  • Advanced Member
  • 2,357 posts
  • Locationwhite lake michigan

Posted 06 April 2006 - 12:16 PM

take a look at the examples, in your code you haven't identified the resource.

[a href=\"http://www.zend.com/manual/function.mysql-fetch-array.php\" target=\"_blank\"]http://www.zend.com/manual/function.mysql-fetch-array.php[/a]

Lite...

all the brothers were valiant!

[br][br]The truely intelligent people are not those who create the dots; rather they are they ones with the ability to connect the dots into a coherent picture

#3 dalton6275

dalton6275
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 April 2006 - 01:08 PM

ah, but im not sure if thats the entire problem. I cant seem 2 be able to perform any kind of date calculation from these mysql sources to find the difference in minutes. Ill read the page and see if that helps but i think i need to edit the date values somehow!

#4 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 06 April 2006 - 01:33 PM

what format are the columns in? if they are DATE or DATETIME columns, you can't perform regular arithmetic operations on it. if they are DATE/TIME columns, you can use some mysql functions to extract the difference in minutes between the two.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#5 dalton6275

dalton6275
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 06 April 2006 - 02:07 PM

yep, DATETIME format in MySQL. Only problem is i need the difference in minutes.

Can i turn these datetimes into a number then perform the calculation like that?



#6 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 06 April 2006 - 02:13 PM

[!--quoteo(post=362223:date=Apr 6 2006, 10:07 AM:name=dalton6275)--][div class=\'quotetop\']QUOTE(dalton6275 @ Apr 6 2006, 10:07 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
yep, DATETIME format in MySQL. Only problem is i need the difference in minutes.

Can i turn these datetimes into a number then perform the calculation like that?
[/quote]

certainly... here's how i would go about it since it's the easiest to understand. keep in mind that strtotime() gives a UNIX timestampe that is the number of seconds since the UNIX epoch (12AM, Jan 1, 1970). with that in mind, we can do calculations... however, this script will ONLY work on dates that are AFTER Jan 1, 1970!
function getMinutesDiff($date1, $date2) {
  $date1 = strtotime($date1);
  $date2 = strtotime($date2);
  $diff = abs($date2 - $date1);

  $min = 60; // 60 seconds in a minute, obviously;-)
  
  return number_format(($diff / $min), 3);
}

while ($row = mysql_fetch_array($Recordset1))
  echo getMinutesDiff($row['outage_start'], $row['outage_end']);


hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users