determining time difference between two timestamp fields.
in [General]
|
Prev: editing a file
Next: Looking for PHP/Solr developer
From: Peter Lind on 25 May 2010 15:14 On 25 May 2010 21:09, Bruce Gilbert <webguync(a)gmail.com> wrote: > the resulting output with that code is a little weird. I get September > 3, 1970 2:39:32pm > > I think part of the problem is my Query. When I run it in PHP MyAdmin > I get a null value for login_timestamp even though there is indeed a > timestamp there. The Query again is: > > SELECT Responses.editor_name,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10,Answer11,Answer12,submit_timestamp,login_timestamp > Â Â Â Â FROM Responses LEFT JOIN Candidates USING (user_id) > > login_timestamp is in a table called 'Candidates' and submit_timestamp > is in a tables called 'Responses'. > > thanks for all the help to this point. > > On Tue, May 25, 2010 at 2:28 PM, Ashley Sheridan > <ash(a)ashleysheridan.co.uk> wrote: >> On Tue, 2010-05-25 at 14:22 -0400, Bruce Gilbert wrote: >>> echo "<tr><th>Completion Time:</th></tr><tr><td>". date('F j, Y >>> g:i:sa',strtotime($row['submit_timestamp']) - >>> strtotime($row['login_timestamp']))/60 , "</td></tr>"; >> >> There's a good reason for that! What you're actually doing is this: >> >> echo "<tr><th>Completion Time:</th></tr><tr><td>" . >> Â date('F j, Y g:i:sa', >> Â Â strtotime($row['submit_timestamp']) - >> Â Â strtotime($row['login_timestamp']) >> Â ) >> Â / 60 >> Â , "</td></tr>"; >> >> You're trying to divide a string by 60, because date() returns a string. >> Put that division inside the brackets for date() rather than outside. >> >> It might help to break up that whole line of output into several parts. >> Put the date into a variable and then just output the HTML line: >> >> $date = date('F j, Y g:i:sa', (strtotime($row['submit_timestamp']) - >> strtotime($row['login_timestamp']))/60); >> echo "<tr><th>Completion Time:</th></tr><tr><td>$date</td></tr>"; >> There are two problems you're looking at. The first is the null value you're getting, which will invalidate the whole thing. The second problem is that you were looking for output along the lines of "60 minutes" but you're using date() which as the second parameter expects a unix timestamp - not two timestamps subtracted from each other. On another note, subtracting the output of two strtotime() calls inside the date() call doesn't amount to subtracting anything from a string. Regards Peter -- <hype> WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 </hype>
From: Nathan Rixham on 25 May 2010 15:18 Ashley Sheridan wrote: > On Tue, 2010-05-25 at 14:22 -0400, Bruce Gilbert wrote: >> echo "<tr><th>Completion Time:</th></tr><tr><td>". date('F j, Y >> g:i:sa',strtotime($row['submit_timestamp']) - >> strtotime($row['login_timestamp']))/60 , "</td></tr>"; > > There's a good reason for that! What you're actually doing is this: > > echo "<tr><th>Completion Time:</th></tr><tr><td>" . > date('F j, Y g:i:sa', > strtotime($row['submit_timestamp']) - > strtotime($row['login_timestamp']) > ) > / 60 > , "</td></tr>"; > > You're trying to divide a string by 60, because date() returns a string. > Put that division inside the brackets for date() rather than outside. > > It might help to break up that whole line of output into several parts. > Put the date into a variable and then just output the HTML line: > > $date = date('F j, Y g:i:sa', (strtotime($row['submit_timestamp']) - > strtotime($row['login_timestamp']))/60); > echo "<tr><th>Completion Time:</th></tr><tr><td>$date</td></tr>"; Ash is right :) If I may add a little observation, this (and many things like it) may be easier to approach and debug if you handle just one operation at a time; break it down in to little chunks. For instance: $submit_time = strtotime($row['submit_timestamp']); $login_time = strtotime($row['login_timestamp']); $completion_time = $submit_time - $login_time; $completion_time /= 60; $formatted_completion_time = date('F j, Y g:i:sa', $completion_time ); echo "<tr><th>Completion Time:</th></tr><tr><td>"; echo $formatted_completion_time; echo "</td></tr>"; Sure it's verbose, but it's also so much easier to debug, and you can always tidy / crunch it up afterwards. Best, Nathan
From: Bruce Gilbert on 25 May 2010 15:50 yea, not sure why my Query isn't returning a value though? If I don't use date(), what should I use? the SQL for the timestamps looks like this. `login_timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `submit_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, sorry for the top-posting, but I think you have to in gmail. On Tue, May 25, 2010 at 3:14 PM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > On 25 May 2010 21:09, Bruce Gilbert <webguync(a)gmail.com> wrote: >> the resulting output with that code is a little weird. I get September >> 3, 1970 2:39:32pm >> >> I think part of the problem is my Query. When I run it in PHP MyAdmin >> I get a null value for login_timestamp even though there is indeed a >> timestamp there. The Query again is: >> >> SELECT Responses.editor_name,Answer1,Answer2,Answer3,Answer4,Answer5,Answer6,Answer7,Answer8,Answer9,Answer10,Answer11,Answer12,submit_timestamp,login_timestamp >> FROM Responses LEFT JOIN Candidates USING (user_id) >> >> login_timestamp is in a table called 'Candidates' and submit_timestamp >> is in a tables called 'Responses'. >> >> thanks for all the help to this point. >> >> On Tue, May 25, 2010 at 2:28 PM, Ashley Sheridan >> <ash(a)ashleysheridan.co.uk> wrote: >>> On Tue, 2010-05-25 at 14:22 -0400, Bruce Gilbert wrote: >>>> echo "<tr><th>Completion Time:</th></tr><tr><td>". date('F j, Y >>>> g:i:sa',strtotime($row['submit_timestamp']) - >>>> strtotime($row['login_timestamp']))/60 , "</td></tr>"; >>> >>> There's a good reason for that! What you're actually doing is this: >>> >>> echo "<tr><th>Completion Time:</th></tr><tr><td>" . >>> date('F j, Y g:i:sa', >>> strtotime($row['submit_timestamp']) - >>> strtotime($row['login_timestamp']) >>> ) >>> / 60 >>> , "</td></tr>"; >>> >>> You're trying to divide a string by 60, because date() returns a string.. >>> Put that division inside the brackets for date() rather than outside. >>> >>> It might help to break up that whole line of output into several parts. >>> Put the date into a variable and then just output the HTML line: >>> >>> $date = date('F j, Y g:i:sa', (strtotime($row['submit_timestamp']) - >>> strtotime($row['login_timestamp']))/60); >>> echo "<tr><th>Completion Time:</th></tr><tr><td>$date</td></tr>"; >>> > > There are two problems you're looking at. The first is the null value > you're getting, which will invalidate the whole thing. The second > problem is that you were looking for output along the lines of "60 > minutes" but you're using date() which as the second parameter expects > a unix timestamp - not two timestamps subtracted from each other. > On another note, subtracting the output of two strtotime() calls > inside the date() call doesn't amount to subtracting anything from a > string. > > Regards > Peter > > -- > <hype> > WWW: http://plphp.dk / http://plind.dk > LinkedIn: http://www.linkedin.com/in/plind > BeWelcome/Couchsurfing: Fake51 > Twitter: http://twitter.com/kafe15 > </hype> > -- ::Bruce::
From: Peter Lind on 25 May 2010 15:54 On 25 May 2010 21:50, Bruce Gilbert <webguync(a)gmail.com> wrote: > yea, not sure why my Query isn't returning a value though? If I don't > use date(), what should I use? The output of strtotime() is an int - specifically a number of seconds. Subtract two number of seconds from each other and what do you get? Furthermore, divide by 60 and what do you get? Should you want to format this, you can consider sprintf(), though, if you just want a whole number of minutes, just use floor(). > the SQL for the timestamps looks like this. > `login_timestamp` datetime NOT NULL default '0000-00-00 00:00:00', > `submit_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, Try just selecting all values from your database, to see what's actually in there. > sorry for the top-posting, but I think you have to in gmail. No you don't, that's ridiculous. -- <hype> WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 </hype>
From: Bruce Gilbert on 25 May 2010 17:03
thanks again for all the help. >The output of strtotime() is an int - specifically a number of >seconds. Subtract two number of seconds from each other and what do >you get? Furthermore, divide by 60 and what do you get? > Should you want to format this, you can consider sprintf(), though, >if you just want a whole number of minutes, just use floor(). I am using floor() now instead of date() > Try just selecting all values from your database, to see what's > actually in there. I found out the problem.the value in (user_id) in the two tables has to be the same. That is why the Query wasn't working. I still am not getting the desired result though. Something that should be displaying as five minutes displays as -117 minutes My current code. $submit_time = strtotime($row['submit_timestamp']); $login_time = strtotime($row['login_timestamp']); $completion_time = $submit_time - $login_time; $completion_time /= 60; $formatted_completion_time = floor($completion_time ); echo "<tr><th class='complete'>Completion Time:</th></tr><tr><td>"; echo $formatted_completion_time; echo " minutes"; echo "</td></tr>"; On Tue, May 25, 2010 at 3:54 PM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > On 25 May 2010 21:50, Bruce Gilbert <webguync(a)gmail.com> wrote: >> yea, not sure why my Query isn't returning a value though? If I don't >> use date(), what should I use? > > The output of strtotime() is an int - specifically a number of > seconds. Subtract two number of seconds from each other and what do > you get? Furthermore, divide by 60 and what do you get? > Should you want to format this, you can consider sprintf(), though, > if you just want a whole number of minutes, just use floor(). > >> the SQL for the timestamps looks like this. >> `login_timestamp` datetime NOT NULL default '0000-00-00 00:00:00', >> `submit_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, > > Try just selecting all values from your database, to see what's > actually in there. > >> sorry for the top-posting, but I think you have to in gmail. > > No you don't, that's ridiculous. > > -- > <hype> > WWW: http://plphp.dk / http://plind.dk > LinkedIn: http://www.linkedin.com/in/plind > BeWelcome/Couchsurfing: Fake51 > Twitter: http://twitter.com/kafe15 > </hype> > -- ::Bruce:: |