CODEWITHSUNDEEP
mysqlwarnings
Straff
Straff

Reputation: 5759

mysql now() Incorrect datetime value for column - error code 1292

When doing simple maths 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)

it 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 wouldn't give selection results despite being expected.

Upvotes: 3

Views: 12862

Answers (2)

Straff
Straff

Reputation: 5759

There's an insidious problem doing simple maths using now() ... subtraction of seconds and minutes etc are based on 100 seconds in a minute, and 100 minutes in an hour ...

Sometimes it seems to work and other times not. 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)

all good, 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 (what looks like a time stamp) with 92 seconds.

Turns out I needed to be doing something more like 

select cdrstatuschangets from cdrs where ( cdrstatuschangets < now() - INTERVAL 50 SECOND );

but it's the intermittent nature of the problem that "hurt".

Upvotes: 6

T.J. Crowder
T.J. Crowder

Reputation: 1074545

I'd recommend using DateAdd instead, both for reliability and readability.

Upvotes: 1

Related Questions