I have two questions regarding Date/Time using SUM function.

SELECT report_id,nt_user_id,report_desc,ts,start_time, stop_time , SUM(stop_time-start_time), SUM(stop_time-ts),SUM(start_time-ts)
FROM rprthistory
GROUP BY report_id,nt_user_id,report_desc,ts,start_time, stop_time

An example of the sum results.

0 00:07:15 0 01:14:32 0 01:07:17

1. Is there a way I can format this data to say something like ...

'0 Days','00 Hours','07 Minutes','15 seconds'?

Not sure if there is date/time function that will allow me to format the date/time results. I'm not really interested in the date. I just want to know how long it took to run.

2. When I run this query, I get a column header called '(SUM)' for the three calculations I'm doing. Is there a way to change the column header within the query? I know this can be done in Sql Server, but not sure if it can be done in Informix.

Thanks.

Matt

you can accomplish #2 by using a column alias after the closing parenthesis that is meaningful, I used RESULT1, RESULT2, RESULT3:

SELECT report_id,nt_user_id,report_desc,ts,start_time, stop_time , SUM(stop_time-start_time) RESULT1, SUM(stop_time-ts) RESULT2,SUM(start_time-ts) RESULT3
FROM rprthistory
GROUP BY report_id,nt_user_id,report_desc,ts,start_time, stop_time

You cannot the column alias to GROUP BY but you can use them to ORDER BY