Time_to_sec() is a very important function in mysql.We
usually use this function to convert a time value to second.But there is a maximum
limit corresponding to this function and that is 3020399 (sec.s)
Now consider a case that we are having a table which
maintains the history record of a project activities and we want to find out the
average time taken by the concerned assignees in hh:mm:ss format!
Here we can’t directly use time_to_sec() function here to find
out the total seconds takes by the current assigners (due to its value
limitation) to finish their work!
So for getting the desired output first we need to calculate the days difference then multiply it with 24*60*60 to convert it into seconds and then adding time_to_sec function value like
DATEDIFF(start_date,end_date)*24*60*60+TIME_TO_SEC(TIMEDIFF(TIME(start_date),TIME(end_date)))
AS sec_diff
Eg. Table with the name A contains following entries
P_ID
|
created
|
closed
|
Ent-1
|
2013-04-01 16:05:18
|
2013-08-08 16:31:04
|
Ent-2
|
2013-05-30 15:38:19
|
2013-08-29 13:08:33
|
Ent-3
|
2013-05-29 17:41:59
|
2013-08-22 21:27:33
|
Ent-4
|
2013-06-12 16:41:34
|
2013-08-26 11:48:14
|
Ent-5
|
2013-06-12 16:02:29
|
2013-08-21 06:25:34
|
Ent-6
|
2013-06-10 16:00:24
|
2013-08-14 16:19:27
|
Ent-7
|
2013-06-13 15:18:41
|
2013-08-12 17:38:14
|
Ent-8
|
2013-07-04 10:31:23
|
2013-09-02 07:00:34
|
Ent-9
|
2013-07-01 18:15:33
|
2013-08-30 08:10:10
|
Ent-10
|
2013-06-24 18:32:13
|
2013-08-22 12:14:19
|
We need to find out the average time taken to complete all
these 10 entries under Project-ID column.
So,the query would be :--
SELECT
CONCAT(SUBSTRING_INDEX(average/(24*60*60),'.',1)*24+
HOUR(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(24*60*60),'.',1)*24*60*60)),':',
MINUTE(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(24*60*60),'.',1)*24*60*60)),':',
SECOND(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(24*60*60),'.',1)*24*60*60))) AS time_average FROM
(
SELECT AVG(sec_diff) AS average FROM
(
SELECT p_id,created,closed,
DATEDIFF(created,closed)*24*60*60+
TIME_TO_SEC(TIMEDIFF(TIME(created),TIME(closed))) AS sec_diff
) AS a
) AS b
CONCAT(SUBSTRING_INDEX(average/(24*60*60),'.',1)*24+
HOUR(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(24*60*60),'.',1)*24*60*60)),':',
MINUTE(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(24*60*60),'.',1)*24*60*60)),':',
SECOND(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(24*60*60),'.',1)*24*60*60))) AS time_average FROM
(
SELECT AVG(sec_diff) AS average FROM
(
SELECT p_id,created,closed,
DATEDIFF(created,closed)*24*60*60+
TIME_TO_SEC(TIMEDIFF(TIME(created),TIME(closed))) AS sec_diff
) AS a
) AS b
So it will produce the result(in hh:mm:ss) format :--
time_average
1806:35:35Do let me know if having any confusion related to this post.
