SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question 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
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this is not a bug. you're asking for the difference in time between two days, but you're not providing the date component.

    for example, say you want to calculate the time between 7 am now and 10 am tomorrow. if you feed timediff() 7:00 and 10:00. you want the answer to be 25 hours, but timediff() returns 3.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    No no

    Perhaps you didn't understood my question.

    Its 11 PM now at the night, after 2 hours it will be 1 am in the midnight, but when you do this calculation in mysql it shows 23 hours difference or so.

    Should not it deduct the time diff and show the result as 2 hours?


    Thanx

  4. #4
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the exact query you're running?
    Cross browser css bugs

    Dan Schulz you will be missed

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Timediff(curtime(),'23:55:42')

  6. #6
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT curtime();
    What does this return for you?
    Cross browser css bugs

    Dan Schulz you will be missed

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    11:37:37

  8. #8
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. It's basically just as longneck said, you need to feed the DATEs too.

    Try:

    Code:
    SELECT TIMEDIFF(
    '2009-02-14 00:30:00',
    '2009-02-13 23:00:00'
    );
    Cross browser css bugs

    Dan Schulz you will be missed

  9. #9
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    One last <offtopic> question:

    If the output is 01:09:16, is there anyway I can convert it to minutes? Any PHP function?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    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
    "giving out my real stuffs"

  11. #11
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    742
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ah!

    That solved my problem.

    MODS: Plz lock this thread.

    Thanx

  12. #12
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Next time, please do more research ( like looking the actual function up in the spec ) before claiming it is a bug.
    Cross browser css bugs

    Dan Schulz you will be missed


Bookmarks

Posting Permissions

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