Find difference between two time enteries

[bobrivers2003]

I have two field in a db, one called begin_time and one called end_time they are both stored as datetime types.

currently two entries are:

begin_time end_time
2007-04-12 12:18:00 2007-04-12 14:39:00

I need to work out the difference in hh:mm:ss between the begin and end times.

I have tried

select date_format(end_time, '%H:%i:%s') - date_format(begin_time, '%H:%i:%s') from tablename where id =1;


it just returns 2 in no format.

How do I get it to return the actual difference in hh:mm:ss?

Any help would be greatly appreciated

Thanks and regards

 

[r937]

there are a couple of ways, but the important questions are: what version of mysql are you on, and will the difference ever exceed 24 hours?

r937.com | rudy.ca

 

[bobrivers2003]

hey there,


the version is 4.1, and the difference will only ever be a couple of hours

Thanks

 

[TonyGroves]

You could use:
[tt]
SELECT TIMEDIFF(end_time,begin_time) FROM ...
[/tt]

 

[r937]

TIMEDIFF() was added in MySQL 4.1.1.

select timediff(end_time,begin_time) as d
  from tablename
 where id = 1

r937.com | rudy.ca

 

[bobrivers2003]

timediff(), fantastic function!!

It also works for periods greater than 24hrs.


Thanks alot for the advice people