MySQL difference between timestamps?

[random1]

I'm trying to find out a way to create a procedure in MYSQL that calculates the difference in years, months, days and seconds between two timestamps.

 

For example time between "2011-01-01 11:11:11" and "2012-05-05 05:05:05".

 

The bits of code I have so far are:

 

SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;

 

SELECT UNIX_TIMESTAMP('2010-11-29 13:16:55') - UNIX_TIMESTAMP('2010-11-29 13:13:55') AS output;

 

SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) AS diff;

 

Also the PHP version of this type of functionality I'm looking for is: http://stackoverflow.com/questions/676824/how-to-calculate-the-difference-between-two-dates-using-php

[joel24]

have a look at this function - just need to modify it to include hours/seconds... and to accept unix timestamps if you're using unix timestamps at all? - from_unixtime()

http://forums.mysql.com/read.php?10,367815,367819#msg-367819

CREATE FUNCTION getDateDifferenceString(date1 DATE, date2 DATE) RETURNS VARCHAR(30)
RETURN CONCAT(
/* Years between */
@years := TIMESTAMPDIFF(YEAR, date1, date2),
IF (@years = 1, ' year, ', ' years, '),
/* Months between */
@months := TIMESTAMPDIFF(MONTH, DATE_ADD(date1, INTERVAL @years YEAR), date2),
IF (@months = 1, ' month, ', ' months, '),
/* Days between */
@days := TIMESTAMPDIFF(DAY, DATE_ADD(date1, INTERVAL @years * 12 + @months MONTH), date2),
IF (@days = 1, ' day', ' days')
)
;

[random1]

Thanks joel24. I'll merge that with my current code