All Forums Database
TD.Aspirant 6 posts Joined 01/10
12 Mar 2010
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

TD Aspirant

mtlrsk 30 posts Joined 06/08
13 Mar 2010

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


mtlrsk 30 posts Joined 06/08
13 Mar 2010

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);

Fred 1096 posts Joined 08/04
14 Mar 2010

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.

TD.Aspirant 6 posts Joined 01/10
15 Mar 2010

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


Fred 1096 posts Joined 08/04
28 Mar 2010

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.

You must sign in to leave a comment.