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 time difference between values in different columns with MySQL
For this, you can use time_format() and time_diff(). To find the time difference, you need to use the time_diff() method. Let us first create a table −
mysql> create table DemoTable624 (PunchIn datetime,PunchOut datetime); Query OK, 0 rows affected (0.60 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable624 values('2019-07-14 12:10:00', '2019-07-14 12:50:00');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable624 values('2019-07-14 11:00:00', '2019-07-14 11:30:00');
Query OK, 1 row affected (0.34 sec)Display all records from the table using select statement −
mysql> select *from DemoTable624;
This will produce the following output −
+---------------------+---------------------+ | PunchIn | PunchOut | +---------------------+---------------------+ | 2019-07-14 12:10:00 | 2019-07-14 12:50:00 | | 2019-07-14 11:00:00 | 2019-07-14 11:30:00 | +---------------------+---------------------+ 2 rows in set (0.00 sec)
Following is the query to get the time difference −
mysql> select time_format(timediff(PunchIn,PunchOut),'%H hrs, %i min.') from DemoTable624;
This will produce the following output −
+-----------------------------------------------------------+ | time_format(timediff(PunchIn,PunchOut),'%H hrs, %i min.') | +-----------------------------------------------------------+ | -00 hrs, 40 min. | | -00 hrs, 30 min. | +-----------------------------------------------------------+ 2 rows in set (0.00 sec)
Updated on: 23-Aug-2019
601 Views
Advertisements