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
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)
Updated on: 30-Jul-2019
368 Views
Advertisements