Re: Calculate elapsed time between 2 time/dates

[Paul DuBois]

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.
---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php