[SOLVED] Calculating SUM time beyond MySQL TIME field limit ?!

[kupilot]

Hi

 

I`m creating a flight logbook for myself where I add flight details including flight start and end as DATETIME fields then add the block time in a TIME field.

 

I know how to do the SUM by using this snippet:

SELECT
SEC_TO_TIME( SUM( TIME_TO_SEC( `block` ) ) ) AS total_time 
FROM t1

 

Yet as a pilot I will accumulate a total flying time more than the limit of the TIME field limits which is according to the MySQL document is 838:59:59 , the total flying hours can run into 10000:00:00 hours and more.

 

Now here is my question , How do I create the correct statment to give the correct total and not run into the limit , shall I change the field type from TIME to something else ?

 

Thanks for any suggestions.

 

[fenway]

I'd store the value as a INT column or minutes or seconds, depending on the resolution you need.

[rajivgonsalves]

try this pretty heavy but should work 

 

SELECT concat(floor(SUM( TIME_TO_SEC( `block` ))/3600),":",floor(SUM( TIME_TO_SEC( `block` ))/60)%60,":",SUM( TIME_TO_SEC( `block` ))%60) AS total_time 
FROM t1

[kupilot]

Thanks rajivgonsalves for the reply , its quite heavy solution.

 

try this pretty heavy but should work 

 

SELECT concat(floor(SUM( TIME_TO_SEC( `block` ))/3600),":",floor(SUM( TIME_TO_SEC( `block` ))/60)%60,":",SUM( TIME_TO_SEC( `block` ))%60) AS total_time 
FROM t1

 

fenway yes I think thats what I should do , make the field as INT type and store the block as minutes and then reformat the output using php , I think thats the only soultion.

 

Thank you all for trying and help.