Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
201 Views
