Datetime conversion issue with USE_TZ and settings.TIME_ZONE != 'UTC'? #371
Description
Software versions
- Django: 4.2.10
- mssql-django: 1.4.2 (problems seems to exist since 1.2.*)
- python: 3.12.1
Table schema and Model
In this example I am using auth_user of Django User-Model
Problem description and steps to reproduce
[positive test] (I would say this is the correct behaviour)
older version - here mssql-django: 1.1.3
In [1]: from django.db.models.functions import Cast
In [2]: from django.db.models import CharField
In [3]: vals = User.objects.annotate(ll_str=Cast('last_login', CharField())).values('ll_str', 'last_login').first()
In [4]: vals
Out[4]:
{'last_login': datetime.datetime(2017, 6, 14, 17, 41, 34, tzinfo=zoneinfo.ZoneInfo(key='Europe/Berlin')),
'll_str': 'Jun 14 2017 5:41PM'}
In [5]: str(User.objects.filter(last_login=vals['last_login']).values('pk').query)
Out[5]: 'SELECT [auth_user].[id] FROM [auth_user] WHERE [auth_user].[last_login] = 2017-06-14 17:41:34' # <-- still same as "ll_str"
In [6]: User.objects.filter(last_login=vals['last_login']).values('pk')
Out[6]: <QuerySet [{'pk': 1}]> # <--- not empty[negative test] (I would say this is NOT the correct behaviour)
mssql-django: 1.2.* and newer
In [1]: from django.db.models.functions import Cast
In [2]: from django.db.models import CharField
In [3]: vals = User.objects.annotate(ll_str=Cast('last_login', CharField())).values('ll_str', 'last_login').first()
In [4]: vals
Out[4]:
{'last_login': datetime.datetime(2017, 6, 14, 17, 41, 34, tzinfo=zoneinfo.ZoneInfo(key='Europe/Berlin')),
'll_str': 'Jun 14 2017 5:41PM'}
In [5]: str(User.objects.filter(last_login=vals['last_login']).values('pk').query)
Out[5]: 'SELECT [auth_user].[id] FROM [auth_user] WHERE [auth_user].[last_login] = 2017-06-14 15:41:34+00:00' # <-- differs from "ll_str"
In [6]: User.objects.filter(last_login=vals['last_login']).values('pk')
Out[6]: <QuerySet []> # <--- emptyrelevant settings probably are
In [7]: connection.timezone_name
Out[7]: 'Europe/Berlin'
In [8]: settings.USE_TZ
Out[8]: True
In [9]: settings.TIME_ZONE
Out[9]: 'Europe/Berlin'Expected behavior and actual behavior
I would expect a match if I built my filter like this User.objects.filter(last_login=User.objects.first().last_login).
Am I doing something wrong here?
In earlier versions (e.g. mssql-django==1.1.3) this seems to work as I expect.
It looks like it interprets the database datetime as settings.TIME_ZONE (in my case Europe/Berlin) without any conversion, but for the query it converts it to UTC.