Django TruncDate gives null

Question

I'm using Django 2.2

I'm filtering records using Django query like

from datetime import datetime
from django.db.models.functions import TruncDate

start_date = datetime.strptime('2020-02-01', '%Y-%m-%d').date()
end_date = datetime.strptime('2020-03-31', '%Y-%m-%d').date()

lead_list = LeadList.objects.all()

# Filter query
query = LeadListEntry.objects.filter(
    lead_list__in=lead_list
  )

# Filter by start date
query = query.filter(
    created__gte=start_date
  )

# Filter by end date
query = query.filter(
    created__lte=end_date
  )

# Annotate date
query = query.annotate(
     created_date=TruncDate('created')
   ).order_by(
     'created_date'
   ).values('created_date').annotate(
     **{'total': Count('created')}
   )

The SQL query generated is

SELECT 
    DATE(CONVERT_TZ(`lead_generation_leadlistentry`.`created`, 'UTC', 'UTC')) AS `created_date`, 
    COUNT(`lead_generation_leadlistentry`.`created`) AS `total`
FROM `lead_generation_leadlistentry` 
WHERE (
    `lead_generation_leadlistentry`.`lead_list_id` IN (
        SELECT 
            U0.`id` FROM `lead_generation_leadlist` U0 
        WHERE 
            U0.`deleted` IS NULL
    )
    AND `lead_generation_leadlistentry`.`created` >= '2020-02-01 00:00:00' 
    AND `lead_generation_leadlistentry`.`created` <= '2020-03-31 00:00:00'
)
GROUP BY DATE(CONVERT_TZ(`lead_generation_leadlistentry`.`created`, 'UTC', 'UTC'))
ORDER BY `created_date` ASC

This is behaving different on local and staging server

Local Development server

+--------------+-------+
| created_date | total |
| ------------ | ----- |
| 2020-02-25   | 15    |
| 2020-02-27   | 10    |
+--------------+-------+

Staging server

+--------------+-------+
| created_date | total |
| ------------ | ----- |
| null         | 15    |
+--------------+-------+

The date column is null

NOTE: Django has timezone enabled by USE_TZ=True

LeadListEntry model

class LeadListEntry(models.Model):
    lead_list = models.ForeignKey(LeadList, on_delete=models.CASCADE, related_name='lead_list_entry')
    data = models.TextField(help_text='Lead List entry data. (JSON data)', blank=False)

    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)

user_3719167 · Answer

Here is the solution for the users getting the same error

The error was not with the Django, instead it was with the MySQL database setup.

The date field returns null because CONVERT_TZ does not work. This can be due to no timezone data in the MySQL database.

To import the timezone data, execute following command

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

After importing, restart the mysql server.

user_4984493 · Answer

The problem of different results from local to staging may happen because of different DateTime of the machine, I got this before when I was developing on windows but the staging was ubuntu, it turned out strptime() date parameters were different and kept showing errors.

For date filtering and as your code sample you don't have to create a date object, just a correct string date format will be enough

start_date = '2020-02-01'
end_date = '2020-03-31'

Then go as normal.

I would like to do something like this,

# As you only need LeadList id's for foreginkey filtering, this is more optimized
lead_list = LeadList.objects.all().values_list('id')

# Filter query
query = LeadListEntry.objects.filter(
    lead_list__in=lead_list, created__date__gte = start_date, created__date__lte=end_date
  )

and to group by date

query = query.values('created__date').annotate(count=Count('created__date')).order_by('created__date')

Django TruncDate gives null

2 Answers