MySQL Time Difference BUG

  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

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
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6254
  • Loc: Seattle, WA

Post 3+ Months Ago

Are you including dates in the comparison? The function is most likely assuming that both times are within the same date, in which case the amount of time from midnight to 11 p.m. truly is 23 hours.
  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

No dates sir, just time.

My Query:

TIMEDIFF(CURTIME(),'23:37:37')
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6254
  • Loc: Seattle, WA

Post 3+ Months Ago

Do you understand what I'm getting at then?
  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

I think so. Do i have to add dates as well? May be use the NOW() function instead of CURTIME()?
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

If you've got the dates handy you'll save yourself a headache.

I tried some trickery with UNIX_TIMESTAMP, ADDTIME, and the modulo operator but MySQL truncates times such as "43:12:34" so I gave up.

Code: [ Select ]
SELECT TIME_FORMAT
(
    (
        UNIX_TIMESTAMP
        (
            ADDTIME
            (
                TIMEDIFF ('23:00:00', CURTIME()),
                '24:00:00'
            )
        ) % 86400
    ),
    '%H:%I:%S'
);
  1. SELECT TIME_FORMAT
  2. (
  3.     (
  4.         UNIX_TIMESTAMP
  5.         (
  6.             ADDTIME
  7.             (
  8.                 TIMEDIFF ('23:00:00', CURTIME()),
  9.                 '24:00:00'
  10.             )
  11.         ) % 86400
  12.     ),
  13.     '%H:%I:%S'
  14. );
  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

Thank you so much for your help.

Appreciate it :)

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: Bill_Thompson and 76 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.