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.
Thread
RE: Calculate elapsed time between 2 time/datesYC Nyon12 Mar
  • Re: Calculate elapsed time between 2 time/datesRoger Baklund13 Mar
    • Re: Calculate elapsed time between 2 time/datesSankaranarayanan Mahadevan13 Mar
    • Re: Calculate elapsed time between 2 time/datesPaul DuBois13 Mar