Search code examples
pythonmysqlpython-3.5pymysql

Reading MySQL via Python sometimes causes warning 1292, seemingly interpreting seconds as greater than 60.

This is a strange one, and though I find several posts dealing with the 1292 Warning, nothing seems to quite fit my scenario.

Setup: Python 3.5 PyMySQL==0.7.11 MySql 5.5.53

Query: 

Executing sql: SELECT PacketID, `Type`, `Length`, Raw_Data
                    FROM extra_packets 
                    WHERE `Time` > NOW() - 10  AND PacketId > '18345' ORDER BY `Time` DESC;

Result:

python3.5/site-packages/pymysql/cursors.py:323: Warning: (1292, "Incorrect datetime value: '20171107190398' for column 'Time' at row 1")

Sample Data:

PacketID    Time                Type    Length  Raw_Data

18,346      2017-11-07 19:04:06 76  22  .......
18,345      2017-11-07 19:04:01 76  22  .......
18,344      2017-11-07 19:03:57 76  22  .......

Notes:

  • In the warning message, the seconds value is greater than 60 - I assume this is the problem?
  • This doesn't always happen, presumably it is time specific
  • I'm not actually using Time in the response -- just for the filter.

Question: (Obviously) How do I correct this warning?

Solution

  • Use a proper INTERVAL expression instead of 10.

    WHERE Time > DATE_SUB(NOW(), INTERVAL 10 SECOND)

    Your code is converting NOW() to an integer like 20171107190408 and then subtracting 10 from that. You'll get the error whenver the seconds of the current time are less than 10.