MySQL Query error when run within Python but not when run directly

Question

I am passing '2012-09-10 00:00:00-05:00' to a MySQL query. This value is retrieved using the pytz module for Python.

import pytz
class MyClass():
    def __init____(self):
        self.tz = pytz.timezone(settings.TIME_ZONE)
        todaystart = self.tz.localize(datetime.now(self.tz).replace(hour=0, minute=0, second=0, microsecond=0, tzinfo=None), is_dst=None).astimezone(self.tz)

The MySQL query is this, after todaystart has been substituted in:

SELECT * FROM mytable WHERE created > UNIX_TIMESTAMP(STR_TO_DATE('2012-09-10 00:00:00-05:00','%Y-%m-%d %k:%i:%s') - INTERVAL 1 DAY);

If I execute this query directly, it returns data as expected. If I put this query into code it gives this error: Warning: Truncated incorrect datetime value: '2012-09-09 00:00:00-05:00'

The code I am using is this (within Django):

query = """SELECT * FROM mytable WHERE created > UNIX_TIMESTAMP(STR_TO_DATE('2012-09-10 00:00:00-05:00','%Y-%m-%d %k:%i:%s') - INTERVAL 1 DAY);"""
myCursor = connections[system_db].cursor()
results = myCursor.execute(query)    # Dies on this statement
resultcount = results.fetchall()

I didn't see an offset format string in the MySQL docs for str_to_date. I would prefer to keep that offset, as the data is returned for a 3rd party system and by keeping it in place I don't have to do any logic between that return and executing the query with the returned date. However, I don't think it has to do with the offset since it works if I run it directly.

What have I done incorrectly that would cause this Warning to appear when Python runs the query against MySQL?

Answer 1

Marc B had it right.

When executed directly, you are getting the warnings, but probably not noticing:

mysql> SELECT * FROM mytable WHERE created > UNIX_TIMESTAMP(STR_TO_DATE('2012-09-10 00:00:00-05:00','%Y-%m-%d %k:%i:%s') - INTERVAL 1 DAY);
Empty set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2012-09-10 00:00:00-05:00'      |
| Warning | 1292 | Truncated incorrect datetime value: '2012-09-10 00:00:00-05:00'      |
| Warning | 1292 | Incorrect datetime value: '1347148800' for column 'created' at row 1 |
+---------+------+----------------------------------------------------------------------+
3 rows in set (0.00 sec)