CODEWITHSUNDEEP
mysql
Hugo Simonin
Hugo Simonin

Reputation: 37

MySQL DateTime between rows

I would like to calculate the time difference between 2 rows in my table.

The table is a basic table with ID and time:

Id_op Heure_deb             Id_act  Type      TIMEDIFF

OP1 2017-04-21 10:35:53 ACT4    Classique
OP1 2017-04-21 10:36:22 ACT3    Formateur
OP1 2017-04-21 14:44:13 ACT1    Classique
OP2 2017-04-21 14:43:54 ACT1    Classique
OP2 2017-04-21 14:44:08 ACT2    Classique
OP2 2017-04-21 14:44:30 ACT2    Classique
OP2 2017-04-21 14:44:34 ACT4    Classique
OP2 2017-04-24 09:11:57 ACT3    Classique

I know the function lag() but it doesn't work with MySQL...

Do you have a solution?

Upvotes: 0

Views: 58

Answers (1)

Pradeep Kumar
Pradeep Kumar

Reputation: 4141

Use TIME_TO_SEC followed by TIMEDIFF()

SELECT a.columns_to_select, TIME_TO_SEC(TIMEDIFF(a.Heure_deb, b.Heure_deb)) as Time_Difference
FROM basic_table a
LEFT JOIN basic_table b ON a.unique_column = b.unique_column
WHERE
a.id = some_value
AND
b.id = some_value

Upvotes: 3

Related Questions