difference of values with datatype Time

Database
Enthusiast

difference of values with datatype Time

Hi All,

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

Thanks,
TD Aspirant
5 REPLIES
Enthusiast

Re: difference of values with datatype Time

sel (time'17:39:07' - time'17:31:10') hour to second;

HTH.
Enthusiast

Re: difference of values with datatype Time

to be more precise, u can also use format commands to format ur answerset accordingly:-

sel (time'17:39:07' - time'17:31:10') hour to second(0);
Teradata Employee

Re: difference of values with datatype Time

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.
Enthusiast

Re: difference of values with datatype Time

@Fred : yes. But, my question is..As to how to save this interval to one more field duration which is a time(0) datatype.

@mtlrsk: I do not want to hard code the time value...

Please help me...I have to get the duration value in Time(0) format.

Thanks..
Teradata Employee

Re: difference of values with datatype 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.