List: General Discussion « Previous MessageNext Message » From: Paul DuBois Date: March 13 2003 5:41pm Subject: Re: Calculate elapsed time between 2 time/dates View as plain text
```At 12:54 +0100 3/13/03, Roger Baklund wrote:
>* YC Nyon
>>  I need to get the time/date (ie. 1 day 12 hours 11 min 4sec)
>>  between 2 time dates.
>>  Can't seem to find any of these functions in the Mysql manual. The nearest
>>  was Period_diff() which is calculating months elapsed.
>
>I don't think you can do it directly, you have to split the day and time
>part, and calculate the two things separately: The number of days between
>the two dates AND the difference in the HH:MM:SS-part of the two dates.
>
>HTH,
>
>--
>Roger

That's correct.  Here's some examples (from MySQL Cookbook):

---

o  Take the difference in days between the date parts of the
values and multiply by 24*60*60 to convert to seconds.

o  Offset  the  result  by the difference in seconds between
the time parts of the values.

Here's an example, using two date-and-time values that lie a
week apart:

mysql> SET @dt1 = '1800-02-14 07:30:00';
mysql> SET @dt2 = '1800-02-21 07:30:00';
mysql> SET @interval =
-> ((TO_DAYS(@dt2) - TO_DAYS(@dt1)) * 24*60*60)
-> + TIME_TO_SEC(@dt2) - TIME_TO_SEC(@dt1);
mysql> SELECT @interval AS seconds;
+---------+
| seconds |
+---------+
|  604800 |
+---------+

To convert the interval from seconds to other units, perform
the appropriate division:

mysql> SELECT @interval AS seconds,
-> @interval / 60 AS minutes,
-> @interval / (60 * 60) AS hours,
-> @interval / (24 * 60 * 60) AS days,
-> @interval / (7 * 24 * 60 * 60) AS weeks;
+---------+---------+-------+------+-------+
| seconds | minutes | hours | days | weeks |
+---------+---------+-------+------+-------+
|  604800 |   10080 |   168 |    7 |     1 |
+---------+---------+-------+------+-------+

I  cheated  here  by choosing an interval that produces nice
integer values for all the division operations.  In general,
you'll have a fractional part, in which case you may find it
helpful to use FLOOR(expr) to chop off the  fractional  part
and produce an integer.
```