I have a table with 3 columns: Start_t time(0), end_t time(0), and duration time(0) I have start_t value as '17:31:10' , end_t as '17:39:07' I want to calculate duration as end_t-start_t => 17:39:07-17:31:10 I am getting the value as '00:07:97'
but 97 is invalid seconds...can anyone please help me in getting the duration also in 'HH:MM:SS' format
First, verify that your column is really a TIME field. (The ODBC parser may substitute FLOAT when you specify TIME if the middle letter in DateTimeFormat is I.) Use SHOW TABLE or HELP COLUMN or query the DBC tables directly. Also note that the difference of two TIME fields will be an INTERVAL, not another TIME.
But duration would be an INTERVAL datatype. It's logically incorrect to assign an INTERVAL value to a TIME field. If you absolutely insist on storing the result as TIME(0) instead of the proper type, you can add the INTERVAL to TIME constant of midnight. Or convert to Character and back, since INTERVAL HOUR(2) TO SECOND and TIME look alike in character form.