Getting average time in seconds format in mysql

Question

How to find out average timestamp the field timestamp in a table gettime

Timestamp
 2010-02-08 14:17:36 | 127.0.0.1 |
 2010-02-08 14:17:30 | 127.0.0.1 |
 2010-02-08 14:17:30 | 127.0.0.1 |

The following query gives some number how to format it and get it in seconds.

  select  avg(timestamp) from gettime;

the above gives some random number .How to format this

Answer 1

From Overview of Date and Time Types

The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, which loses the part after the first nonnumeric character.) To work around this problem, you can convert to numeric units, perform the aggregate operation, and convert back to a temporal value.

Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;

SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;