CODEWITHSUNDEEP
mysqlsqlsql-timestamp
T. Hægh
T. Hægh

Reputation: 109

Calculate minutes between two or more timestamps in SQL

My question: is there a way to calculate the difference between two timestamps with a SQL query? I have a database table (MySQL 5.6) with different timestamps:

id  start_time          end_time            employee
1   2020-02-01 06:00:00 2020-02-01 13:00:00 Mark
2   2020-02-01 06:00:00 2020-02-01 12:30:00 David
3   2020-02-01 07:00:00 2020-02-01 14:00:00 Donald
4   2020-02-01 07:00:00 2020-02-01 13:00:00 Harry

I want to calculate the difference in minutes between every record in the table, i.e.: the difference of record with id 1 is 13:00:00 - 06:00:00 = 7 hours = 420 minutes. I want to calculate this for every record in the table and at the end summing up all the minutes. How can I do this?

I've already tried 

SELECT TIMESTAMPDIFF(MINUTE, '2020-01-02 06:00:00', '2020-01-02 13:00:00');

but how to use this with all the records without hardcoding all the timestamps by myself?

Can I use something like 

SELECT TIMESTAMPDIFF(MINUTE, 'start_time', 'end_time') AS totalMinutes ?

Many thanks in advance!

Upvotes: 0

Views: 1998

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I would expect this to do what you want:

select t.*,
       timestampdiff(minute, start_time, end_time) as minutes
from t;

You can incorporate this into a view, if you want it readily available:

create v_t as
    select t.*,
           timestampdiff(minute, start_time, end_time) as minutes
    from t;

In later versions, you can add a computed column:

alter table t add minutes int generated always as
    (timestampdiff(minute, start_time, end_time));

Upvotes: 2

Related Questions