Syntax error Calculate total time duration (add time) in MySQL?

Calculate total time duration (add time) in MySQL?

To calculate the total time duration in MySQL, you need to use SEC_TO_TIME(). Let us see an example by creating a table

mysql> create table AddTotalTimeDemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > LoginTime time
   - > );
Query OK, 0 rows affected (0.63 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into AddTotalTimeDemo(LoginTime) values('05:05:00');
Query OK, 1 row affected (0.10 sec)
mysql> insert into AddTotalTimeDemo(LoginTime) values('07:20:00');
Query OK, 1 row affected (0.16 sec)
mysql> insert into AddTotalTimeDemo(LoginTime) values('02:05:00');
Query OK, 1 row affected (0.17 sec)
mysql> insert into AddTotalTimeDemo(LoginTime) values('03:03:00');
Query OK, 1 row affected (0.25 sec)
mysql> insert into AddTotalTimeDemo(LoginTime) values('05:07:00');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from AddTotalTimeDemo;

The following is the output

+----+-----------+
| Id | LoginTime |
+----+-----------+
|  1 | 05:05:00  |
|  2 | 07:20:00  |
|  3 | 02:05:00  |
|  4 | 03:03:00  |
|  5 | 05:07:00  |
+----+-----------+
5 rows in set (0.00 sec)

The following is the query to calculate the total time duration in MySQL

mysql> SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(LoginTime))) AS TotalTime from AddTotalTimeDemo;

The following is the output

+-----------+
| TotalTime |
+-----------+
| 22:40:00  |
+-----------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements