MySQL datetime arithmetics

Question

Can someone, please, explain me this behavior of MySQL?

Query:

SELECT NOW() + INTERVAL 90 SECOND - NOW() AS test;

Result:

• 130 - in cases when now() is in (00:00 - 00:29) interval

• 170 - in cases when now() is in (00:30 - 00:59) interval

I interpret the 130 result as "1 minute and 30 second". The 170 result is strange, I suspect it has something to do with minute overflow but I can not explain it.

Following query works OK (I mean - as I expected):

select TIMEDIFF( DATE_ADD( now(), INTERVAL 90 SECOND), now()) AS test;

Result: 00:01:30

Answer 1

Initially, it seems to me the reason this is failing is because you can't subtract when the interval precedes the date.

SELECT INTERVAL 90 SECOND - NOW(); //..right syntax to use near '- NOW()'

The manual states:

INTERVAL expr unit is permitted on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr unit is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.

SELECT CAST(NOW() + INTERVAL 90 SECOND AS DATETIME) - CAST(NOW() AS DATETIME)

I also would most likely interpret 130 as 1 minute, 30 seconds, but what appears to be happening is that straight arithmetic is performed by subtracting the time parts (as integers, not time).

For 130:

SELECT CAST(NOW() + INTERVAL 90 SECOND AS DATETIME)
//June, 05 2013 13:10:50+0000

SELECT CAST(NOW() AS DATETIME)
//June, 05 2013 13:09:20+0000

SELECT CAST(NOW() + INTERVAL 90 SECOND AS DATETIME) - CAST(NOW() AS DATETIME)
//130

  1050
-  920
------
   130

For 170:

SELECT CAST(NOW() + INTERVAL 90 SECOND AS DATETIME)
//June, 05 2013 13:12:11+0000

SELECT CAST(NOW() AS DATETIME)
//June, 05 2013 13:10:41+0000

SELECT CAST(NOW() + INTERVAL 90 SECOND AS DATETIME) - CAST(NOW() AS DATETIME)
//170

   1211
-  1041
-------
    170

However, you should be using INTERVAL using the date functions (DATE_ADD, DATE_SUB) as they were intended seeing how something like above returns 130/170 when it should return 90.