MySQL difference between two timestamps in Seconds?

You can use in-built function UNIX_TIMESTAMP() from MySQL to get the timestamps and the difference between two timestamps. The syntax is as follows −

SELECT UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2) as anyVariableName from yourTableName;

To understand the above concept, let us create a table. The following is the query to create a table −

mysql> create table DifferenceInSeconds
   −> (
   −> FirstTimestamp TIMESTAMP,
   −> SecondTimestamp TIMESTAMP
   −> );
Query OK, 0 rows affected (0.93 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into DifferenceInSeconds values('2012-12-12 13:16:55','2012-12-12 13:13:55');
Query OK, 1 row affected (0.31 sec)

mysql> insert into DifferenceInSeconds values('2014-10-11 12:15:50','2014-10-11 12:13:50');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DifferenceInSeconds values('2018-12-14 13:30:53','2018-12-14 13:27:53');
Query OK, 1 row affected (0.21 sec)

Now display all records from the table using select statement. The query is as follows −

mysql> select *from DifferenceInSeconds;

The following is the output −

+---------------------+---------------------+
| FirstTimestamp      | SecondTimestamp     |
+---------------------+---------------------+
| 2012-12-12 13:16:55 | 2012-12-12 13:13:55 |
| 2014-10-11 12:15:50 | 2014-10-11 12:13:50 |
| 2018-12-14 13:30:53 | 2018-12-14 13:27:53 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

Here is the query to find the difference between two timestamp in seconds. The query is as follows −

mysql> SELECT UNIX_TIMESTAMP(FirstTimestamp) - UNIX_TIMESTAMP(SecondTimestamp) as Seconds from DifferenceInSeconds;

The following is the output −

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

Note - If you do not know which timestamp is greater then use ABS().

The syntax is as follows −

SELECT ABS(UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2)) as Seconds from DifferenceInSeconds;

To check the above syntax, let us insert record in which first timestamp has lower value.

mysql> insert into DifferenceInSeconds values('2018-12-14 13:26:53','2018-12-14 13:31:53');
Query OK, 1 row affected (0.21 sec)

The query to display all records from the table.

mysql> select *from DifferenceInSeconds;

The following is the output −

+---------------------+---------------------+
| FirstTimestamp      | SecondTimestamp     |
+---------------------+---------------------+
| 2012-12-12 13:16:55 | 2012-12-12 13:13:55 |
| 2014-10-11 12:15:50 | 2014-10-11 12:13:50 |
| 2018-12-14 13:30:53 | 2018-12-14 13:27:53 |
| 2018-12-14 13:26:53 | 2018-12-14 13:31:53 |
+---------------------+---------------------+
4 rows in set (0.00 sec)

The following is the use of ABS() function. The query is as follows −

mysql> SELECT ABS(UNIX_TIMESTAMP(FirstTimestamp) - UNIX_TIMESTAMP(SecondTimestamp)) as Seconds from DifferenceInSeconds;

The following is the output −

+---------+
| Seconds |
+---------+
|     180 |
|     120 |
|     180 |
|     300 |
+---------+
4 rows in set (0.00 sec)

Note - If you will not use ABS() then -300 seconds will be the above output.

Jennifer Nicholas

Published on 16-Jan-2019 09:49:31