MySQL Query error when starting in Python, but not when starting directly

Question

I pass '2012-09-10 00:00:00-05:00' to the 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) 

MySQL query is, after todaystart been replaced by:

 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 request in code, it will give this error: Warning: Truncated incorrect datetime value: '2012-09-09 00:00:00-05:00'

The code I use is this (in 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 do not see the format offset in the MySQL docs for str_to_date. I would prefer to keep this offset, since the data is returned for a third-party system and, keeping it in place, I do not need to do any logic between this return and the execution of the query with the returned date. However, I don’t think this is due to the bias, since it works if I run it directly.

What did I do wrong to cause this Warning appear when Python launches a query to MySQL?

Answer 1

Mark B had this right.

When executed directly, you get warnings, but you probably don't notice:

 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)