asked on

mysql query to convert a duration in days to years, months & days

Hi,
I have a table which has two columns which show a start date and an end date.
I need to show the duration between the two dates as 3 columns (years, months, days)

Can you help?

So far I have converted the start date and end date to a duration in days, as follows, next I plan to convert the days to years/months/days but i'm not sure how to

This is what I have

Select start_date, end_date, DATEDIF(end_date, start_date)+1 as duration

but how do I turn the duration into three columns Years, Months, Days ?

Thanks

woepwobin

SELECT TIMESTAMPDIFF(YEAR, start_date, end_date) AS year,
    MOD(TIMESTAMPDIFF(MONTH, start_date, end_date), 12) AS month,
    MOD(TIMESTAMPDIFF(MONTH, start_date, end_date), 365) AS day;

Open in new window

No support for leap year unfortunately.

MeDude21

Try This

Select TIMESTAMPDIFF(Year,'2011-01-01 08:30:30', '2011-02-01 16:30:30');
Select TIMESTAMPDIFF(Month,'2011-01-01 08:30:30', '2011-02-01 16:30:30');
Select TIMESTAMPDIFF(Day,'2011-01-01 08:30:30', '2011-02-01 16:30:30');
Select TIMESTAMPDIFF(Hour,'2011-01-01 08:30:30', '2011-02-01 16:30:30');

EICT

ASKER

Hi,
Neither are giving me quite the right answer.
If I take the start date 2011-01-01 and end date 2012-02-10, I know that it is 1year 1 month and 10 days but I'm getting the total number of years or months or days from the duration not a break down.
For example its working about that the duration is 1whole year or 1 whole month or 40 days.
Both give the answer 1year 1 month 40 days which is not what I expect.

I hope this makes sense?

woepwobin

Yes, it makes sense, except I disagree about the 10 days. I think it's 9 days :)

I see now I made a mistake. Try this:

mysql> SET @from_date='2011-01-01 00:00:00';
mysql> SET @to_date='2012-02-10 00:00:00';
mysql> SELECT TIMESTAMPDIFF(YEAR, @from_date, @to_date) AS year,
    MOD(TIMESTAMPDIFF(MONTH, @from_date, @to_date), 12) AS month,
    MOD(MOD(TIMESTAMPDIFF(DAY, @from_date, @to_date), 12), 365) AS day;
+------+-------+------+
| year | month | day  |
+------+-------+------+
|    1 |     1 |    9 |
+------+-------+------+
1 row in set (0.00 sec)

mysql>

Open in new window

ASKER CERTIFIED SOLUTION