Hai..

i have 2 columns with 'time' datatype in my MySQL table one storing the start time and the other one storing the end time of an event. I store time values in these 2 colums in 'HH:MMS' format.

i have to find out the time difference between these two columns (endtime - starttime). (ie) the total time interval for which the process was running.

i am using the following query for that.

select time_format(etime-stime,'%H') as hour , time_format(etime-stime,'%i') as minutes from tablename";

this is working fine for values in which the endtime minutes is higher than the starttime minutes, eg (starttime - 08:30:00 and endtime - 10:40:00) but when the endtime minute is less than the starttime minute, (starttime - 08:30:00 and endtime - 10:20:00) i am getting null values. So how do over come this. Please help...

With regards
Sudar

Hello,

Why not doing this :

select time_format(etime-stime,'%H:%i') as elapsed_time from tablename;

?

Regards,

RBARAER

hai RBARAER

thnks fot the reply
i
tried even
select time_format(etime-stime,"%H:%i") from table_name

even for this also, it is returning NULL values when the minute in etime is less than the minute in stime.


BTW i am using MySQL V 4.0.17

with regards
Sudar

This seems odd to me ... In the first case it could have been somewhat logical (and you would not have had the correct result with this query). But the second should work...

Try this, which will show you the elapsed time in seconds :

select TIME_TO_SEC(etime) - TIME_TO_SEC(stime) as elapsed_seconds from tablename;

And if the result seems correct to you, try :

select TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(etime) - TIME_TO_SEC(stime)), '%H:%i') as elapsed_time from tablename;

You're sure datatype is well TIME ? Not DATETIME or other ?

Regards,

RBARAER

thnks RBARAER

your second solution is working fine and has solved my problem..

thnk u once again

With regards
Sudar