1

The Extract function of Django ORM returns None after annotation if I use DateTimeField. Why??

models.py

class SimpleModel(models.Model):
    date_value = models.DateField()
    datetime_value = models.DateTimeField()

settings.py

DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': 'testdb',
            'USER': 'root',
            'PASSWORD': 'password',
        }
    }

Django shell output [connected to MYSQL]

In [5]: from django.db.models.functions import  Extract                                                                                               

In [6]: annotate_kwargs = dict(month_from_datetime_field=Extract('datetime_value','month'),month_from_date_field=Extract('date_value','month'))                   

In [7]: SimpleModel.objects.annotate(**annotate_kwargs)[0].__dict__                                                                                   
Out[7]: 
{'_state': <django.db.models.base.ModelState at 0x7fbb8ffb3eb8>,
 'id': 1,
 'date_value': datetime.date(2019, 11, 26),
 'datetime_value': datetime.datetime(2019, 11, 26, 6, 9, 14, tzinfo=<UTC>),
 'month_from_datetime_field': None,
 'month_from_date_field': 11}
funnydman
  • 9,083
  • 4
  • 40
  • 55
JPG
  • 82,442
  • 19
  • 127
  • 206
  • Just to note: `date` and `datetime` names of the python standard libraries and it's a good idea to avoid using them as custom variable names. – funnydman Nov 26 '19 at 06:30
  • 1
    that doesn't change the result, thnx for the advice though – JPG Nov 26 '19 at 06:35
  • Are you sure that datetime_value is a required field? I see the only one situation where it is possible - `datetime_value` is null (None) value. – funnydman Nov 26 '19 at 06:43
  • It's `null=False` by default – JPG Nov 26 '19 at 06:44
  • 1
    Can you please check this https://stackoverflow.com/questions/14454304/convert-tz-returns-null. I checked the example on Postgres it works just fine, maybe this is MySQL related issue? – funnydman Nov 26 '19 at 06:54
  • 1
    maybe this one helps: https://stackoverflow.com/questions/45681227/extractyear-and-extractmonth-returning-none-in-django – Nalin Dobhal Nov 26 '19 at 07:20
  • So, my next question would be why Django uses [`CONVERT_TZ` mysql function](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz) only with **`DateTimeField`** – JPG Nov 26 '19 at 07:25
  • `DateField` field based on the python's `datetime.date`, which does not store info about the timezone. Any `DateTimeField` field value when support for time zones is enabled(default) will be converted and stored in a database with UTC timezone. For reference: https://docs.djangoproject.com/en/2.2/topics/i18n/timezones/#overview – funnydman Nov 26 '19 at 07:45

0 Answers0