query using subtime() unexpectedly returns no rows

I'm using the following query:

 SELECT message,timestamp 
 FROM botlogs.tbllogs
 WHERE message like '%Failed to grab car amount 
       or 0 website%' and timestamp > Subtime('2011-08-01 13:20','0:60')

Although I have a row for 8/1/2011 1:21:53pm, the above query doesn't return any rows. Why is that?

If I run

select timestamp,message 
from botlogs.tbllogs 
where message like Failed to g开发者_开发百科rab car amount 
       or 0 website%' and timestamp < '2011-08-01 13:20' - INTERVAL 180 SECOND

it returns the error from 7/31/2011 9:27:24 pm.

Because:

mysql> SELECT Subtime('2011-08-01 13:20','0:60');
+------------------------------------+
| Subtime('2011-08-01 13:20','0:60') |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set, 1 warning (0.04 sec)

Try this instead

mysql> SELECT Subtime('2011-08-01 13:20','00:01:00');
+----------------------------------------+
| Subtime('2011-08-01 13:20','00:01:00') |
+----------------------------------------+
| 2011-08-01 13:19:00                    |
+----------------------------------------+
1 row in set (0.00 sec)

Or

mysql> SELECT '2011-08-01 13:20' - INTERVAL 60 SECOND;
+-----------------------------------------+
| '2011-08-01 13:20' - INTERVAL 60 SECOND |
+-----------------------------------------+
| 2011-08-01 13:19:00                     |
+-----------------------------------------+
1 row in set (0.01 sec)

From the docs,

• SUBTIME(expr1,expr2)

SUBTIME() returns expr1 – expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time expression.

(emphasis mine)

The problem is that 0:60 is not a valid time expression:

mysql> select time('0:60');
+--------------+
| time('0:60') |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '0:60' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

and so the subtraction returns NULL:

mysql> select subtime(now(), '0:60');
+------------------------+
| subtime(now(), '0:60') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '0:60' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

The NULLs cause your comparison to return false for every possibility, which results in 0 rows in the final result.