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...

...you 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

Huh?

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. I am sure there is a very good technical explanation, but….

LOL!