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.
