Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question Unanswered: MySQL Time Difference BUG

    Hi

    Not sure if this is a bug or not.


    I am doing a sql query for time difference using the TIMEDIFF function.

    It works for all other but not when not when you are doing a time difference for start time 23:00:00 (11 PM night) and end time 00:30:00 (12 AM Midnight)

    if you take a look, the difference is 1.5 hours, but the sql tells me that the difference is 22 hours, 30 minutes



    Any solution to this?

    Thanx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cancer10
    Not sure if this is a bug or not.
    i bet it isn't
    Code:
    CREATE TABLE test_timediff
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , start_datetime DATETIME
    , end_datetime DATETIME
    );
    INSERT INTO test_timediff VALUES
     ( 1, '2009-02-13 23:00:00','2009-02-13 00:30:00' )
    ,( 2, '2009-02-14 23:00:00','2009-02-13 00:30:00' )
    ,( 3, '2009-02-13 23:00:00','2009-02-14 00:30:00' )
    ;
    SELECT t.*
         , TIMEDIFF(start_datetime,end_datetime) AS diff1
         , TIMEDIFF(end_datetime,start_datetime) AS diff2
      FROM test_timediff AS t
    ;
    /* results:
    id start_datetime      end_datetime          diff1     diff2
     1 2009-02-13 23:00:00 2009-02-13 00:30:00  22:30:00 -22:30:00
     2 2009-02-14 23:00:00 2009-02-13 00:30:00  46:30:00 -46:30:00
     3 2009-02-13 23:00:00 2009-02-14 00:30:00 -01:30:00  01:30:00
    */
    any questions?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    36
    This is now resolved.


    Thanx

Posting Permissions

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