Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: difference between two dates

    What is the best way to find the difference between two dates in a database??

    I have a 'received' and a 'replied' field and i want to find the response time

    Please can anyone help

  2. #2
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    I don't know if this is the best method, but I usually want the difference in seconds and then UNIX_TIMESTAMP() is the way to go.

    So
    SELECT id, UNIX_TIMESTAMP(replied)-UNIX_TIMESTAMP(received) AS resptime FROM tab;
    would give you the difference between the two times in seconds.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can then use the SEC_TO_TIME function to convert the seconds to HH:MM format

    note: this works only if the seconds are less than 86400 (one day), but you can use CASE expressions with some further math to extract the remainder (modulus)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    DATEDIFF() was added in MySQL 4.1.1.

    DATEDIFF(expr,expr2)
    DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

    mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
    -> 1
    mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
    -> -31

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •