Calculate the time that passed between first row inserted in MySQL and the last

SELECT TIMEDIFF( MAX(time_col), MIN(time_col)) FROM [tablename];

This will be helpful when it stores in time-sequentially. But timestamp column can be modified by updating, so I suggest the below query than above one.

SELECT TIMEDIFF( x.time_col, y.timecol ) FROM 
   
( SELECT time_col FROM [tablename] ORDER BY [primary_key] DESC LIMIT 1) x,
   
( SELECT time_col FROM [tablename] ORDER BY [primary_key] ASC  LIMIT 1) y

You can use TIMESTAMPDIFF function either.

SELECT TIMESTAMPDIFF( SECOND, x.time_col, y.timecol ) FROM 
   
( SELECT time_col FROM [tablename] ORDER BY [primary_key] DESC LIMIT 1) x,
   
( SELECT time_col FROM [tablename] ORDER BY [primary_key] ASC  LIMIT 1) y

An example to get the time taken from an email send list. The job id is assigned each time a group of emails is sent. It will display the job id, number emails sent, and the time it took from start to finish.

SELECT jid, count(id) as email_count, TIMEDIFF( MAX(insert_date), MIN(insert_date)) as time 
FROM emails GROUP BY jid;

Source

 

Visit sunny St. George, Utah, USA

Get In Touch

We would love to hear from you.

Please enter your email address.
Please enter a valid email address.
Please enter the verification number.
You have entered a wrong verification number.
Please enter a message.
Please check your message for any errors.
Your message has been sent successfully.
Sorry, error occured this time sending your message.
* required