Get the difference between two timestamps in seconds in MySQL?

To get difference between two timestamps in seconds, use two in-built functions TIME_TO_SEC() and TIMEDIFF() in MySQL. The syntax is as follows −

select time_to_sec(timediff(yourCoulnName1,yourCoulnName2)) as anyVariableName from yourTableName;

To understand the above concept, let us first create a table. The query to create a table.

mysql> create table TimeToSecond
   −> (
   −> MyTime timestamp,
   −> YourTime timestamp
   −> );
Query OK, 0 rows affected (0.48 sec)

Now you can insert some datetime values in the table. The query is as follows −

mysql> insert into TimeToSecond values('2016-05-10 10:02:00','2016-05-10 10:00:00');
Query OK, 1 row affected (0.15 sec)

mysql> insert into TimeToSecond values('2016-05-10 10:06:00','2016-05-10 10:03:00');
Query OK, 1 row affected (0.24 sec)

mysql> insert into TimeToSecond values('2018-05-10 11:00:00','2018-05-10 10:00:00');
Query OK, 1 row affected (0.08 sec)

After inserting, you can check how many records are present in the table with the help of select statement. The query to display all records is as follows −

mysql> select *from TimeToSecond;

The following is the output −

+---------------------+---------------------+
| MyTime              | YourTime            |
+---------------------+---------------------+
| 2016-05-10 10:02:00 | 2016-05-10 10:00:00 |
| 2016-05-10 10:06:00 | 2016-05-10 10:03:00 |
| 2018-05-10 11:00:00 | 2018-05-10 10:00:00 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

Let us now get the difference between two timestamps in seconds using the syntax we discussed above. The query is as follows −

mysql> select time_to_sec(timediff(MyTime,YourTime)) as DifferenceInSeconds from TimeToSecond;

The following is the output −

+---------------------+
| DifferenceInSeconds |
+---------------------+
|                 120 |
|                 180 |
|                3600 |
+---------------------+
3 rows in set (0.00 sec)