id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
22370	Mysql TimeZone problems	info@…	nobody	"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.

"	Bug	closed	Database layer (models, ORM)	1.6	Normal	duplicate	Mysql Timezone SQL generation		Unreviewed	0	0	0	0	0	0