- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to get the Average on MySQL time column?
152 Lectures 16 hours
87 Lectures 5.5 hours
To get average on time column, use the below syntax. It will give the average in time format −
SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;
To understand the above concept, let us create a table. The following is the query −
mysql> create table AverageOnTime −> ( −> PunchInTime time −> ); Query OK, 0 rows affected (0.61 sec)
Insert time values in the table using insert command. The query to insert records is as follows −
mysql> insert into AverageOnTime values('00:00:40'); Query OK, 1 row affected (0.20 sec) mysql> insert into AverageOnTime values('00:02:50'); Query OK, 1 row affected (0.15 sec) mysql> insert into AverageOnTime values('00:03:30'); Query OK, 1 row affected (0.13 sec) mysql> insert into AverageOnTime values('00:04:55'); Query OK, 1 row affected (0.14 sec)
Display time from the table using select statement. The query is as follows −
mysql> select *from AverageOnTime;
The following is the output −
+-------------+ | PunchInTime | +-------------+ | 00:00:40 | | 00:02:50 | | 00:03:30 | | 00:04:55 | +-------------+ 4 rows in set (0.00 sec)
Now to get the average on time column, use the below query. The query is as follows −
mysql> SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(PunchInTime))) as Average from AverageOnTime;
The following is the output displaying the average -
+---------------+ | Average | +---------------+ | 00:02:58.7500 | +---------------+ 1 row in set (0.08 sec)
- Get the Average of Average in a single MySQL row?
- How to get Column name on ResultSet in Java with MySQL?
- How do I get the average string length in MySQL?
- Get MAX() on column in two MySQL tables?
- Get the average row length of a MySQL table
- How to get a rating average in MongoDB based on duplicate ids?
- MySQL query to check how to get time difference
- MySQL query to select average from distinct column of table?
- How to get time in milliseconds using C++ on Linux?
- How do I alter table column datatype on more than 1 column at a time in MySql?
- Find the average of column values in MySQL using aggregate function
- How to calculate time based on seconds in MySQL?
- How do I get the current time zone of MySQL?
- Calculate average of numbers in a column MySQL query?
- How to get the creation time of recently created table in MySQL?