determining time difference between two timestamp fields.
in [General]
|
Prev: editing a file
Next: Looking for PHP/Solr developer
From: Bruce Gilbert on 25 May 2010 09:55 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. thanks. -- ::Bruce::
From: Peter Lind on 25 May 2010 10:01 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>
From: Bruce Gilbert on 25 May 2010 10:14 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>"; 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::
From: Peter Lind on 25 May 2010 11:11 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>
From: Bruce Gilbert on 25 May 2010 12:46
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:: |