Surpassing time_to_sec() function maximum limit in Mysql

2013-09-02T23:30:00.001+05:30

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/(246060),'.',1)24+
HOUR(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(246060),'.',1)246060)),':',
MINUTE(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(246060),'.',1)246060)),':',
SECOND(SEC_TO_TIME(average-SUBSTRING_INDEX(average/(246060),'.',1)246060))) AS time_average FROM
(
SELECT AVG(sec_diff) AS average FROM
(
SELECT p_id,created,closed,
DATEDIFF(created,closed)2460*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:35

Do let me know if having any confusion related to this post.

MySQL Hits/Tricks/Queries/Problems

Surpassing time_to_sec() function maximum limit in Mysql