Mysql now () Invalid datetime value for column - error code 1292
When doing simple math using now () ...
mysql>
select cdrstatuschangets from cdrs where ( cdrstatuschangets < now() - 10 );
+---------------------+
| cdrstatuschangets |
+---------------------+
| 2009-09-25 13:55:50 |
+---------------------+
1 row in set (0.00 sec)
show warnings;
Empty set (0.00 sec)
he often worked, but sometimes ...
mysql>
select cdrstatuschangets from cdrs where ( cdrstatuschangets < now() - 50 );
+---------------------+
| cdrstatuschangets |
+---------------------+
| 2009-09-25 13:55:50 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message | |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '20090925211564.000000' for column 'cdrStatusChangeTS' at row 1 |
+---------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
and sometimes does not yield selection results despite expected results.
+2
source to share
2 answers
There's an insidious problem doing simple maths using now () ... subtracting seconds and minutes, etc., is based on 100 seconds per minute and 100 minutes per hour ...
Sometimes it works and sometimes it doesn't. Insidious.
mysql> select now(); select now() -10;
+---------------------+
| now() |
+---------------------+
| 2009-09-25 21:07:20 |
+---------------------+
1 row in set (0.00 sec)
+-----------------------+
| now() -10 |
+-----------------------+
| 20090925210710.000000 |
+-----------------------+
1 row in set (0.00 sec)
everything is fine, but ...
mysql> select now(); select now() -10;
+---------------------+
| now() |
+---------------------+
| 2009-09-25 21:08:02 |
+---------------------+
1 row in set (0.00 sec)
+-----------------------+
| now() -10 |
+-----------------------+
| 20090925210792.000000 |
+-----------------------+
1 row in set (0.00 sec)
Shows a timestamp (which looks like a timestamp) with 92 seconds.
It turns out I needed to do something more like
select cdrstatuschangets from cdrs where ( cdrstatuschangets < now() - INTERVAL 50 SECOND );
but it is the intermittent nature of the problem that "hurts".
+4
source to share