0

I am trying to make this overview of "work" hours

However I can't get it to calculate the hours between two dates in my sql db, but it wont return the result.

I tried to just display the two SQL entries which worked, but it seems like the calculation wont use the SQL enteries

I am getting this error in the top of my page:

Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct() [<a href='datetime.--construct'>datetime.--construct</a>]: Failed to parse time string (31/10-2012 19:14) at position 0 (3): Unexpected character' in D:\xampp\htdocs\admin.php:46 Stack trace: #0 D:\xampp\htdocs\admin.php(46): DateTime->__construct('31/10-2012 19:1...') #1 {main} thrown in D:\xampp\htdocs\admin.php on line 46

What am I doing wrong here??

The thing I need help to is on line 43-52 Here is my code:

<?php $host="localhost"; $username="xxxx"; $password="xxxx"; $db_name="xxxxx"; $tbl_name="log"; mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT * FROM $tbl_name ORDER BY id ASC"; $result=mysql_query($sql); ?> <title>VT Log - Oversigt</title> <table width="90%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td width="30%" align="center" bgcolor="#E6E6E6"><strong>Navn</strong></td> <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Start</strong></td> <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Slut</strong></td> <td width="30%" align="center" bgcolor="#E6E6E6"><strong>Kommentar</strong></td> <td width="15%" align="center" bgcolor="#E6E6E6"><strong>Tid</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center" bgcolor="#FFFFFF"><? echo $rows['user']; ?><BR></td> <td align="center" bgcolor="#FFFFFF"><? echo $rows['start']; ?></td> <td align="center" bgcolor="#FFFFFF"><? echo $rows['end']; ?></td> <td align="center" bgcolor="#FFFFFF"><? echo $rows['comment']; ?></td> <?php $stamp1 = $rows['start']; $stamp2 = $rows['end']; $date1 = new DateTime($stamp2); $date2 = new DateTime($stamp2); $diff = $date2->diff($date1); $hours = $diff->h; $hours = $hours + ($diff->d*24); ?> <td align="center" bgcolor="#FFFFFF"><? echo $hours;?></td> </tr> <?php } mysql_close(); ?> <tr> <td colspan="5" align="center" bgcolor="#E6E6E6"><?php echo "Total arbejdstid: " . array_sum($numbers) . "\n Timer";?></td> </tr> </table>
Improve this question
9
  • 1
    What's the problem? Nothing returned? The wrong result? The right result, in the wrong place? Commented Oct 31, 2012 at 19:00
  • 1
    could let mysql do the work for you and use datediff() or timediff() dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html Commented Oct 31, 2012 at 19:01
  • Sorry forgot the write whats the problem: I tried to just display the two SQL entries which worked, but it seems like the calculation wont use the SQL enteries Commented Oct 31, 2012 at 19:02
  • 1
    Could it be caused by you initialising $date1 and $date2 to the same thing? Commented Oct 31, 2012 at 19:02
  • Dagon - It means "end" :-) I am getting this error on my page: Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct() [<a href='datetime.--construct'>datetime.--construct</a>]: Failed to parse time string (31/10-2012 19:14) at position 0 (3): Unexpected character' in D:\xampp\htdocs\admin.php:46 Stack trace: #0 D:\xampp\htdocs\admin.php(46): DateTime->__construct('31/10-2012 19:1...') #1 {main} thrown in D:\xampp\htdocs\admin.php on line 46 Commented Oct 31, 2012 at 19:03

3 Answers 3

Reset to default
3

Convert both dates to unix timestamps, then return the difference in hours.

SELECT (UNIX_TIMESTAMP(end) - UNIX_TIMESTAMP(start)) / 60.0 / 60.0 as hours_difference

No need for any PHP calculations after that...

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

or use timediff() , no conversion needed
So I'd simply just need to parse that into a new SQL query and use the result from there?
Yes if that's your goal. You can even use this SELECT as part of another query. i.e SELECT * FROM some_table WHERE completed_hours > (SELECT (UNIX_TIMESTAMP(end) - UNIX_TIMESTAMP(start)) / 60.0 / 60.0)
Good point Dagon - TIMEDIFF() works - no need to convert to timestamps first, but then you have to convert the resulting DATE object into hours. Same difference... :)
Ryan, I did this and now I get this: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\xampp\htdocs\admin.php on line 36 .... Line 36: while($rows=mysql_fetch_array($result)){
|
1

Change 

$date1 = new DateTime($stamp2);

to this

 $date1 = new DateTime($stamp1);
Improve this answer

7 Comments

I am still having this error at the top of my page: Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct() [<a href='datetime.--construct'>datetime.--construct</a>]: Failed to parse time string (31/10-2012 19:14) at position 0 (3): Unexpected character' in D:\xampp\htdocs\admin.php:46 Stack trace: #0 D:\xampp\htdocs\admin.php(46): DateTime->__construct('31/10-2012 19:1...') #1 {main} thrown in D:\xampp\htdocs\admin.php on line 46
$date1 = new DateTime($stamp1);
Seems like the DateTime function doesn't like the way your date string is formatted. Why does the datetime have both slashes and dashes? is that the standard format for your location?
try this kind of manipulation and see $date1 = new DateTime(str_replace($stamp1, "/", "-"));
Would it work if I only use the dashes? The manipulation gave me this: Parse error: syntax error, unexpected ';' in D:\xampp\htdocs\admin.php on line 43 - Line 43 is the $date1 = xxxxxxxxxx
|
1

Problem solved! Many thanks Ryan Griggs

Change SQL query to $sql="SELECT *, (UNIX_TIMESTAMP(end) - UNIX_TIMESTAMP(start)) / 60.0 / 60.0 as hours_difference FROM $tbl_name ORDER BY id ASC";

Replace 

<?php $stamp1 = $rows['start']; $stamp2 = $rows['end']; $date1 = new DateTime($stamp2); $date2 = new DateTime($stamp2); $diff = $date2->diff($date1); $hours = $diff->h; $hours = $hours + ($diff->d*24); ?> <td align="center" bgcolor="#FFFFFF"><? echo $hours;?></td>

with

<td align="center" bgcolor="#FFFFFF"><?$var = number_format($rows['hours_difference'],2); $var = number_format($var,1); echo $var; ?></td>
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.