determing time difference between timestamp login and submit with form

[webguync]

Hi,

 

I am using a timestamp in MySQL to determine when a user logs-in and submits data from a form, What I would like to do is determine the time difference between the login and submit timestamps and echo out the results. "The time taken to complete is 45 minutes." for example.

 

How is this done?

 

The timestamp(s) will look something like

2010-05-17 11:32:45

 

[teamatomic]

use what you get from the db with strtotime() to get a real timestamp. subtract it from the form submit timestamp. What you get will be in seconds. simple math from there.

 

 

HTH

Teamatomic

[webguync]

so I would just use SQL to extract the info from the database with $row = mysql_fetch_assoc($result)

 

and display the info, something like

 

<td>{$row['Timestamp']}</td>

 

then where would strtotime need to go? Also, how would I set up the subtraction between the two fields?

 

Thanks

[PFMaBiSmAd]

Or you could just do this in your query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timestampdiff

[webguync]

MYSQL query might be easier.

 

I want something like

SELECT TIMESTAMPDIFF(MINUTE,'2010-05-17 12:26:13','2010-05-17 11:32:45');

 

except will need to use PHP to determine which record to use from WHERE username = '$username' and also the two fields I need to do the addition are in two different tables(Candidates.login_timestamp and Responses.submit_timestamp). Want to be able to echo out the results in PHP

[litebearer]

is your scenario (A) an online continuous; OR (b) logs in, leaves, comes back some othe time, day, week etc?

[webguync]

it would be A - logs in and continuous

[litebearer]

in that case simply use a couple of session variables to accomplish your task. no need to use the db.

session var1 = login timestamp, var2 = submit tiimestamp , ans (in seconds) = var2 - var1. then some simple math to convert ans to hrs:min:sec

[webguync]

with the second timestamp submit_timestamp isn't set until the data hits the database. Can I still use the Session variables?

 

The first timestamp for login is set like this.

 

$sql_update ="UPDATE Candidates
          SET login_timestamp = DATE_ADD(NOW(), INTERVAL 2 HOUR)
          WHERE username = '$username'
          AND password = '$password'"; 

[webguync]

Just using the info in the database fields can I take this row of data.

<td>" . date('F j, Y g:i:sa', strtotime($row["login_timestamp"] )) . "</td>

 

and subtract from another timestamp (submit_timestamp)?...if so what is the proper syntax for that?

 

I tried this, but not working.

 

echo "<tr><th>Completion Time:</th></tr><tr><td>" . date('F j, Y g:i:sa', strtotime($row["login_timestamp"] - ["submit_timestamp")) . "</td></tr>";

 

 

[webguync]

sorry, but to further clarify. I have two timestamps in two different MySQL tables(Responses,Candidates). I am using SQL to SELECT the two timestamps here.

$sql = "SELECT submit_timestamp,login_timestamp 
        FROM Responses LEFT JOIN Candidates USING (user_id)";

 

trying to determine the difference b/t the time from login to submit in minutes.(this is a form). I have done a lot of google-ing, but haven't come up with any solutions yet.

 

any ideas?