Thursday, May 10, 2012

Mysql Makes Me Go "WAT?" Sometimes...

I am very well aware that there is a TIMEDIFF function in MySql to calculate the difference of two dates, and I am sure there is a very good explanation for what follows... and if you know what that is please feel free to share all the details... BUT... would think that IF in whatever language you have a subtraction operation between dates that returns a number… and if you take a date D, you add a day to it and you subtract D from it, you'd get something that means "1 day" in whatever unit (seconds, minutes, something…)

You'd also think that the unit would be meaningful and usable somehow.

Well… this is not MySql's opinion apparently, or at least it is not in any common sense, predictable or understandable way. 

Check this out:
mysql> select (NOW()+INTERVAL 1 DAY)-NOW();
=> 1000000

ok… so apparently 1 day is 1,000,000 units… right?
Not so much...
mysql> select (NOW()+INTERVAL 12 HOUR)-NOW();
=> 120000

12 hours are 120,000 somethings. Huh? Let's try some more cases:
mysql> select (NOW()+INTERVAL 8 HOUR)-NOW();
=> 80000 
mysql> select (NOW()+INTERVAL 1 HOUR)-NOW();
=> 10000 

What about one minute?
mysql> select (NOW()+INTERVAL 1 MINUTE)-NOW();
=> 100 

That is 100 units… wat? What about 59 seconds…
mysql> select (NOW()+INTERVAL 59 SECOND)-NOW();
=> 99 

Ok… that's odd but maybe some kind of rounding thing going on? Let's try some other cases…
mysql> select (NOW()+INTERVAL 58 SECOND)-NOW();
=> 58

mysql> select (NOW()+INTERVAL 57 SECOND)-NOW();
=> 97 

WAT? How did we go from 99 to 58 to 97?
mysql> select (NOW()+INTERVAL 56 SECOND)-NOW();
=> 96 
mysql> select (NOW()+INTERVAL 51 SECOND)-NOW();
=> 91 
mysql> select (NOW()+INTERVAL 49 SECOND)-NOW();
=> 49

Remember this 49...
mysql> select (NOW()+INTERVAL 49 SECOND)-NOW();
=> 89

Look at that, now it is 89… :) 
mysql> select (NOW()+INTERVAL 1 SECOND)-NOW();
=> 1

AND of course, since we are in seemingly random-land:

mysql> set @x = now(); select ( @x + INTERVAL 49 SECOND) - @x;
=> 20120510130600

… yeah, that makes total sense.

So, why does that happen? The reason is actually simple. When a date is converted into a number, MySql converts it in a surprising way:

mysql> select NOW();
=> 2012-05-12 15:03:01

mysql> select NOW()+0;
=> 20120512150301

It takes the current date+time MM-DD-YYYY hh:mm:ss and strings it into an integer in this form: YYYYMMDDhhmmss

So, one hour difference ends up being a difference of 10,000, one day difference is a difference of 1,000,000, one minute difference is 60, and differences in days or months end up looking pretty random.

No comments:

Post a Comment