CODEWITHSUNDEEP
mysql
Christopher
Christopher

Reputation: 788

MySQL calculation minute using TIMEDIFF()

I am trying to calculate total time a student is in class based from differences in timestamp times.

Here is the table which I am using...

CREATE TABLE Students(
  studentID varchar(255), 
  firstName varchar(255), 
  lastName varchar(255), 
  updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  total_time INTEGER DEFAULT NULL
);

ex.) student record at 9:39AM is updated to 9:40AM. The dif here is 1 min... so 1 gets added to the students total_time.

The sql statement I currently have is...

UPDATE Students SET total_time = TIMEDIFF(CURTIME(), updated_at);

However, TIMEDIFF returns a Time variable if I am correct... how can I turn that to minutes? ALSO... would this work even with the correct casting to minutes? Because of the "ON UPDATE" method used for the updated_at column, I would imagine the timestamp would just be updated, then the calculated difference would always be 0, since the two would be the same.

UPDATE: It is vital to mention these records will likely be updated more than once-per minute.

Upvotes: 1

Views: 94

Answers (1)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

try this query to get minutes use this:

UPDATE Students SET total_time = TIMEDIFF(NOW(), updated_at)/60;

to get seconds use this:

UPDATE Students SET total_time = TIMEDIFF(NOW(), updated_at)/1;

Upvotes: 1

Related Questions