Time Management - incorrect MySQL behavior

[NorthStarDA]

Hello again folks,

I have a query for a report that totals times and puts them into a graph, Im using the TIME_TO_SEC function to do the math and it appears that the SEC_TO_TIME function is not working properly when the seconds is 6 digits long. Here is my query and a sample output is below:

SELECT h.project_id,h.time_in,h.time_out,p.name,p.id,
SEC_TO_TIME(SUM(TIME_TO_SEC(h.time_out)-TIME_TO_SEC(h.time_in))) TOTAL_TIME, 
SUM(TIME_TO_SEC(h.time_out)-TIME_TO_SEC(h.time_in)) AS TOTAL_SECONDS 
FROM work_history h,projects p 
WHERE h.work_date BETWEEN #CreateODBCDate(URL.from)# AND #CreateODBCDate(URL.to)# 
AND p.ID = h.project_id 
GROUP BY p.ID ORDER BY p.name

This query works fine (thanks tony groves) in the fact that it gives me total times worked on any given project in the dates i specify, but this is my output:

name       TOTAL_TIME  TOTAL_SECONDS
project1   08:40:10 -- 31210
project2   20:08:13 -- 72493
project3   14:13:56 -- 51236
project4   05:42:19 -- 20539
project5   09:23:49 -- 33829
project6   06:40:44 -- 24044
project7   01:55:22 -- 6922
project8   09:54:39 -- 35679
project9   09:13:44 -- 33224
project10   13:56:23 -- 136583

The total seconds I believe to be accurate and for the most part, TOTAL_TIME is accurate also, except for project 10. It is showing 136583 seconds in that project which comes to just under 38 hours, but the TOTAL_TIME is only showing under 14 hours. It seems that any TOTAL_SECONDS value greater than 6 digits comes out wrong in TOTAL_TIME.

Can anyone help?

 

[TonyGroves]

When I did the following query:
[tt]select sec_to_time(10000),sec_to_time(100000),sec_to_time(1000000)[/tt]
I got the following results:
02:46:40 27:46:40 277:46:40
which are right.

Since 136583 equals 37:56:23, it looks like the return values from your SEC_TO_TIME calls have a "days" portion which is not being shown.

Could it be that your program is just not printing the days portion?

 

[TonyGroves]

Sorry, ignore the bits above.

 

[NorthStarDA]

thanks for the reply tony, i took my formatting off of the output to show it as the database sends it and this is what i get:

Projects  	Total Time
project1 	{ts '1970-01-01 13:48:30'} -- 49710
project2 	{ts '1970-01-02 05:43:30'} -- 107010
project3 	{ts '1970-01-01 17:06:57'} -- 61617
project4 	{ts '1970-01-01 05:42:19'} -- 20539
project5 	{ts '1970-01-01 11:29:45'} -- 41385
project6 	{ts '1970-01-01 10:06:46'} -- 36406
project7 	{ts '1970-01-01 04:00:30'} -- 14430
project8 	{ts '1970-01-01 13:45:25'} -- 49525
project9 	{ts '1970-01-01 11:54:34'} -- 42874
project10 	{ts '1970-01-03 14:24:14'} -- 224654

sure enough it looks like there is a date part in the output showing the number of days. Is SEC_TO_TIME supposed to behave like that?

 

[NorthStarDA]

i missed your last question there, I think I can get my program to display it, it would be easier if it weren't like 1970-01-03. I'll need to extract the DAY part and if it is greater than 1, i'll need to multiply the value by 24 and add to the total hours- how come your version did not output days, but mine did?

 

[TonyGroves]

It must be the ODBC driver that does the conversion. My query went directly to the server.

 

[NorthStarDA]

yes i just tried it straight from the server as well and it worked properly, looks like i'll be on a few more forums.

thanks a million