#22370 closed Bug (duplicate)
Mysql TimeZone problems
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.6 |
Severity: | Normal | Keywords: | Mysql Timezone SQL generation |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I am using Python3.2, django 1.6 and the connector from MySQL
I have a model object with a date
q=Order.objects.datetimes('date','year') q[0]
This results in the following error:
File "<console>", line 1, in <module> File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 132, in __getitem__ return list(qs)[0] File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 96, in __iter__ self._fetch_all() File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 854, in _fetch_all self._result_cache = list(self.iterator()) File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter raise ValueError("Database returned an invalid value " ValueError: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions for your database and pytz installed?
I have pytz installed and have loaded the tables in mysql. In the log I see a following query:
SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`beadmixer_order`.`date`, 'UTC', 'UTC'), '%%Y-01-01 00:00:00') AS DATETIME) FROM `beadmixer_order` ORDER BY 1 DESC LIMIT 21; args=('UTC',)
The %%Y seems to be the culprit:
>>> from django.db import connection >>> cursor = connection.cursor() >>> cursor.execute("SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`abc_order`.`date`, 'UTC', 'UTC'), '%Y-01-01 00:00:00') AS DATETIME) FROM `abc_order` ORDER BY 1 DESC LIMIT 21") >>> str(cursor.fetchone()) '(datetime.datetime(2014, 1, 1, 0, 0, tzinfo=<UTC>),)' >>> cursor.execute("SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`abc_order`.`date`, 'UTC', 'UTC'), '%%Y-01-01 00:00:00') AS DATETIME) FROM `abc_order` ORDER BY 1 DESC LIMIT 21") >>> str(cursor.fetchone()) '(None,)'
I tried a small modification:
The code for mysql.connector.django.base
def datetime_trunc_sql(self, lookup_type, field_name, tzname): # Django 1.6 if settings.USE_TZ: field_name = "CONVERT_TZ({0}, 'UTC', %s)".format(field_name) params = [tzname] else: params = [] fields = ['year', 'month', 'day', 'hour', 'minute', 'second'] #my modification format_ = ('%Y-', '%m', '-%%d', ' %H:', '%%i', ':%%s') #format_ = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') format_def = ('0000-', '01', '-01', ' 00:', '00', ':00') try: i = fields.index(lookup_type) + 1 except ValueError: sql = field_name else: format_str = ''.join([f for f in format_[:i]] + [f for f in format_def[i:]]) sql = "CAST(DATE_FORMAT({0}, '{1}') AS DATETIME)".format( field_name, format_str) return sql, params
This seems to solve some problems:
>>> q=Order.objects.datetimes('date','year') >>> q[0] datetime.datetime(2014, 1, 1, 0, 0, tzinfo=<UTC>)
This is ok. But the following still fails:
>>> q=Order.objects.datetimes('date','day') >>> q[0] Traceback (most recent call last): File "<console>", line 1, in <module> File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 132, in __getitem__ return list(qs)[0] File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 96, in __iter__ self._fetch_all() File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 854, in _fetch_all self._result_cache = list(self.iterator()) File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter raise ValueError("Database returned an invalid value " ValueError: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions for your database and pytz installed?
The corresponding query:
SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`beadmixer_order`.`date`, 'UTC', 'UTC'), '%Y-%m-%%d 00:00:00') AS DATETIME) FROM `beadmixer_order` ORDER BY 1 ASC LIMIT 1; args=('UTC',)
I can't change %%d in %d in 'becamysql.connector.django.base' because this results in a formatting exception.
The code in django.db.backends.mysql
def datetime_trunc_sql(self, lookup_type, field_name, tzname): if settings.USE_TZ: field_name = "CONVERT_TZ(%s, 'UTC', %%s)" % field_name params = [tzname] else: params = [] fields = ['year', 'month', 'day', 'hour', 'minute', 'second'] format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') # Use double percents to escape. format_def = ('0000-', '01', '-01', ' 00:', '00', ':00') try: i = fields.index(lookup_type) + 1 except ValueError: sql = field_name else: format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]]) sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str) return sql, params
seems similar enough to have the same problems.
Change History (4)
comment:1 by , 11 years ago
comment:2 by , 11 years ago
I have had no other problems using the MySQL connector. I started out using Django with the included MySQL backend. However that one had problems in Python 3.
Furthermore the code for eg. datetime_trunc_sql seems very similar, so I wondered if the same problem could exist in the default MySQL backend as well.
comment:3 by , 11 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
I can reproduce the test failure of datetimes.tests.DateTimesTests.test_21432
on Django 1.6 with Connector/Python 1.1.6. Not sure if the bug is in our code or the connector, but in any case, I'm going to mark this as a duplicate of #12500. (anonymous is timo)
comment:4 by , 10 years ago
modify function as_sql in compiler.py as
return ' '.join(result).replace('%%','%'), tuple(params)
Last time I tried (several months ago), the Django test suite did not pass using the MySQL connector. Thus, I am not sure we should be recommending it in our docs (or at least add some caveats).
Besides this issue, does it mostly work for you?