determining time difference between two timestamp fields.
in [General]
|
Prev: editing a file
Next: Looking for PHP/Solr developer
From: Ashley Sheridan on 25 May 2010 12:55 On Tue, 2010-05-25 at 12:46 -0400, Bruce Gilbert wrote: > Here is what I currently have. > > echo "<tr><th>Completion Time:</th></tr><tr><td>" . > (strtotime($row['submit_timestamp']) - > strtotime($row['login_timestamp']))/60 , "</td></tr>"; > > this gives me an output of 21235172.75 > > not sure what format that is in? I was hoping for something like 60 > minutes, 30 minutes etc. Don't need the days or seconds. The MySQL > timestamp is in this format. > > 2010-05-17 11:32:45 - 2010-05-17 12:26:13 > > On Tue, May 25, 2010 at 11:11 AM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > > On 25 May 2010 16:14, Bruce Gilbert <webguync(a)gmail.com> wrote: > >> Thanks. I know my syntax isn't quite right, but is this close to what > >> I need to do? > >> > >> echo "<tr><th>Completion Time:</th></tr><tr><td>" . date('F j, Y > >> g:i:sa', strtotime($row["login_timestamp"] - ["submit_timestamp"])/60) > >> . "</td></tr>"; > >> > > > > No. Assuming that your timestamp is of the YYYY-mm-dd HH:ii:ss form, > > you need to do (strtotime(["submit_timestamp"]) - > > strtotime($row["login_timestamp"]))/60. > > > > Regards > > Peter > > > >> > >> On Tue, May 25, 2010 at 10:01 AM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > >>> On 25 May 2010 15:55, Bruce Gilbert <webguync(a)gmail.com> wrote: > >>>> Here is the situation. I have a form which sets a timestamp when a > >>>> user logs in using UPDATE in SQL. The field is called > >>>> 'login_timestamp' and is in a table called 'Candidates'. I have > >>>> another timestamp which is set when a user submits the form data into > >>>> the DB and it is called 'submit_timestamp' . What I want to do is > >>>> determine the amount of time the user takes to complete the form by > >>>> subtracting the 'login_timestamp' time form the 'submit_timestamp' > >>>> time. I am using SQL to extract the data here. > >>>> > >>>> $sql = "SELECT Responses.name,Answers,submit_timestamp,login_timestamp > >>>> FROM Responses LEFT JOIN Candidates USING (user_id)"; > >>>> > >>>> and then to display the timestamp in readable form. > >>>> > >>>> echo "<tr><th>Completion Time:</th></tr><tr><td>" . date('F j, Y > >>>> g:i:sa', strtotime($row["login_timestamp"])) . "</td></tr>"; > >>>> > >>>> so I need to know how to subtract from two timestamp fields, two > >>>> different tables and come up with the difference in minutes. > >>>> > >>> > >>> In case you're using MySQL, timediff can do the job: > >>> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff > >>> > >>> Otherwise, just do strtotime(endtime) - strtotime(starttime) / 60. > >>> That's the difference in minutes. > >>> > >>> 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:: > >> > > > > > > > > -- > > <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:: > The value returned from strtotime() is a timestamp, the value you output from MySQL isn't a timestamp, it's a string-formatted timestamp. If you need to format a timestamp use the date() function. Thanks, Ash http://www.ashleysheridan.co.uk
From: Bruce Gilbert on 25 May 2010 14:22 probably not fully understanding what I need to do. I am trying 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>"; but just returns a zero value. On Tue, May 25, 2010 at 12:55 PM, Ashley Sheridan <ash(a)ashleysheridan.co.uk>wrote: > On Tue, 2010-05-25 at 12:46 -0400, Bruce Gilbert wrote: > > Here is what I currently have. > > echo "<tr><th>Completion Time:</th></tr><tr><td>" . > (strtotime($row['submit_timestamp']) - > strtotime($row['login_timestamp']))/60 , "</td></tr>"; > > this gives me an output of 21235172.75 > > not sure what format that is in? I was hoping for something like 60 > minutes, 30 minutes etc. Don't need the days or seconds. The MySQL > timestamp is in this format. > > 2010-05-17 11:32:45 - 2010-05-17 12:26:13 > > On Tue, May 25, 2010 at 11:11 AM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > > On 25 May 2010 16:14, Bruce Gilbert <webguync(a)gmail.com> wrote: > >> Thanks. I know my syntax isn't quite right, but is this close to what > >> I need to do? > >> > >> echo "<tr><th>Completion Time:</th></tr><tr><td>" . date('F j, Y > >> g:i:sa', strtotime($row["login_timestamp"] - ["submit_timestamp"])/60) > >> . "</td></tr>"; > >> > > > > No. Assuming that your timestamp is of the YYYY-mm-dd HH:ii:ss form, > > you need to do (strtotime(["submit_timestamp"]) - > > strtotime($row["login_timestamp"]))/60. > > > > Regards > > Peter > > > >> > >> On Tue, May 25, 2010 at 10:01 AM, Peter Lind <peter.e.lind(a)gmail.com> wrote: > >>> On 25 May 2010 15:55, Bruce Gilbert <webguync(a)gmail.com> wrote: > >>>> Here is the situation. I have a form which sets a timestamp when a > >>>> user logs in using UPDATE in SQL. The field is called > >>>> 'login_timestamp' and is in a table called 'Candidates'. I have > >>>> another timestamp which is set when a user submits the form data into > >>>> the DB and it is called 'submit_timestamp' . What I want to do is > >>>> determine the amount of time the user takes to complete the form by > >>>> subtracting the 'login_timestamp' time form the 'submit_timestamp' > >>>> time. I am using SQL to extract the data here. > >>>> > >>>> $sql = "SELECT Responses.name,Answers,submit_timestamp,login_timestamp > >>>> FROM Responses LEFT JOIN Candidates USING (user_id)"; > >>>> > >>>> and then to display the timestamp in readable form. > >>>> > >>>> echo "<tr><th>Completion Time:</th></tr><tr><td>" . date('F j, Y > >>>> g:i:sa', strtotime($row["login_timestamp"])) . "</td></tr>"; > >>>> > >>>> so I need to know how to subtract from two timestamp fields, two > >>>> different tables and come up with the difference in minutes. > >>>> > >>> > >>> In case you're using MySQL, timediff can do the job: > >>> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff > >>> > >>> Otherwise, just do strtotime(endtime) - strtotime(starttime) / 60. > >>> That's the difference in minutes. > >>> > >>> 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:: > >> > > > > > > > > -- > > <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:: > > > > The value returned from strtotime() is a timestamp, the value you output > from MySQL isn't a timestamp, it's a string-formatted timestamp. If you need > to format a timestamp use the date() function. > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > -- ::Bruce::
From: Ashley Sheridan on 25 May 2010 14:28 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>"; Thanks, Ash http://www.ashleysheridan.co.uk
From: Bruce Gilbert on 25 May 2010 15:09 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>"; > > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > > -- ::Bruce::
From: Ashley Sheridan on 25 May 2010 15:05
On Tue, 2010-05-25 at 15:09 -0400, Bruce Gilbert 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>"; > > > > > > Thanks, > > Ash > > http://www.ashleysheridan.co.uk > > > > > > > > > > > > -- > ::Bruce:: > What type is that field that your date is in in the database? ps. please try not to top-post, it breaks the normal flow of the mailing list, and probably kills fairies too! Thanks, Ash http://www.ashleysheridan.co.uk |