Django: Trunc datetime results in none

Question

I have the following model:

class Statistic(models.Model):
  meter = models.ForeignKey(Meter, on_delete=models.CASCADE, db_index=True)
  timestamp_start = models.DateTimeField(db_index=True)
  timestamp_end = models.DateTimeField(db_index=True)
  usage_start = models.DecimalField(max_digits=10, decimal_places=3)
  usage_end = models.DecimalField(max_digits=10, decimal_places=3)
  return_start = models.DecimalField(max_digits=10, decimal_places=3)
  return_end = models.DecimalField(max_digits=10, decimal_places=3)

I have the following value in the first record of timestamp_start in my sqllite database

2016-12-22T06:15:30.420+02:00

When I run the following query:

statistics = Statistic.objects.filter(id=1)
  .annotate(timestamp=Trunc('timestamp_start','day', output_field=DateTimeField()))
  .values('timestamp')
  .annotate(usage_start = Min('usage_start'))

timestamp results in None

QuerySet [{'usage_start': Decimal('136.972'), 'timestamp': None}]

When I attach the debugger and check the first records of my statistics table like this

Statistics.objects.all()[0].timestamp_start

it returns:

datetime.datetime(2016, 12, 22, 6, 0)

Before facing this problem I got this exception:

Database returned an invalid datetime value. Are time zone definitions for your database and pytz installed?

I do have pytz installed. But no idea how to fix this. Is the value incorrect ? Anyway in the end I set use_tz in settings.py to false. The exception vanisched but trunc returns none now. Is this causing the problem?

p.s.: My environment Python 3, Django, Sql lite database, windows

score 2 · Accepted Answer · answered Oct 05 '17 at 17:16

You probably haven't loaded the timezone data into mysql. TruncDate uses the CONVERT_TZ function under the hood, which returns NULL if no timezone data is available. You can check what SQL the query is using by evaluating the following:

str(statistics.query)

Windows instructions for loading timezone data can be found here:

https://dev.mysql.com/downloads/timezones.html
https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html#time-zone-installation (Make sure you scroll down the section which talks about Windows)

I ran into this issue on Linux and resolved it as indicated in this other StackOverflow post:

convert_tz returns null

I forgot about this question. But you are right. I ended up digging through the source code of django. There I found that it indeed requires a timezone in order to function. But thanks for you answer, hopefully it will help others. — Maarten Kieft, Oct 07 '17 at 18:25

Django: Trunc datetime results in none

1 Answers1