2

I am trying to do group my data on the basis of Year, Month and a Column Value. The Query is

 FeedbackData.objects.annotate(year=ExtractYear('created'), month=ExtractMonth('created')).values('year','month','start_operator_alias').annotate(dcount=Count('*')).values('year', 'month','start_operator_alias','dcount')

The result that I am getting is:

<QuerySet [{'year': None, 'start_operator_alias': 7, 'dcount': 12858, 'month': None}, {'year': None, 'start_operator_alias': 2, 'dcount': 185042, 'month': None}, {'year': None, 'start_operator_alias': 5, 'dcount': 13963, 'month': None}, {'year': None, 'start_operator_alias': 3, 'dcount': 127819, 'month': None}, {'year': None, 'start_operator_alias': 0, 'dcount': 566040, 'month': None}, {'year': None, 'start_operator_alias': 6, 'dcount': 83877, 'month': None}, {'year': None, 'start_operator_alias': 1, 'dcount': 170064, 'month': None}, {'year': None, 'start_operator_alias': -1, 'dcount': 36550, 'month': None}, {'year': None, 'start_operator_alias': 4, 'dcount': 25714, 'month': None}, {'year': None, 'start_operator_alias': 8, 'dcount': 200, 'month': None}]>

As you can see the month and year are returned as None. What can be the reason for that or what am I doing wrong.

model:
class FeedbackData(models.Model):
  deviceId = models.CharField(blank=True, null=True, max_length=64, db_tablespace="indexes")
  start_cellId = models.CharField(null=True, blank=True, max_length=32)
  end_cellId = models.CharField(null=True, blank=True, max_length=32)
  start_mcc = models.CharField(null=True, blank=True, max_length=32)
  end_mcc = models.CharField(null=True, blank=True, max_length=32)
  start_mnc = models.CharField(null=True, blank=True, max_length=32)
  end_mnc = models.CharField(null=True, blank=True, max_length=32)
  start_lac = models.CharField(null=True, blank=True, max_length=32)
  end_lac = models.CharField(null=True, blank=True, max_length=32)
  start_operator_name = models.CharField(db_index=True, null=True, max_length=32, blank=True)
  start_operator_alias = models.IntegerField(db_index=True, null=True, default=-1)
  end_operator_name = models.CharField(db_index=True, null=True, max_length=32, blank=True)
  end_operator_alias = models.IntegerField(db_index=True, null=True, default=-1)
  start_signal_strength = models.CharField(null=True, blank=True, max_length=32)
  end_signal_strength = models.CharField(null=True, blank=True, max_length=32)
  average_signal_strength = models.CharField(null=True, blank=True, max_length=32)
  phone_type = models.CharField(db_index=True, null=True, blank=True, max_length=8, default='unknown')
  call_duration = models.IntegerField(db_index=True, null=True, blank=True, default=0)
  network_type = models.CharField(db_index=True, null=True, blank=True, max_length=16, default='unknown')
  start_cell_signal_strength = models.CharField(null=True, blank=True, max_length=32)
  end_cell_signal_strength = models.CharField(null=True, blank=True, max_length=32)
  device_model = models.CharField(null=True, blank=True, max_length=64)
  device_manufacturer = models.CharField(db_index=True, null=True, blank=True, max_length=64)
  os_version = models.CharField(db_index=True, null=True, blank=True, max_length=32)
  start_time_of_call = models.DateTimeField(blank=True, null=True)
  end_time_of_call = models.DateTimeField(blank=True, null=True)
  start_lon = models.FloatField(db_index=True, null=True, blank=True)
  start_lat = models.FloatField(db_index=True, null=True, blank=True)
  end_lon = models.FloatField(db_index=True, null=True, blank=True)
  end_lat = models.FloatField(db_index=True, null=True, blank=True)
  start_lon_rad = models.FloatField(db_index=True, null=True, blank=True) 
  start_lat_rad = models.FloatField(db_index=True, null=True, blank=True)
  end_lon_rad = models.FloatField(db_index=True, null=True, blank=True)
  end_lat_rad = models.FloatField(db_index=True, null=True, blank=True)
  start_cdma_lon = models.FloatField(db_index=True, null=True, blank=True)
  start_cdma_lat = models.FloatField(db_index=True, null=True, blank=True)
  end_cdma_lon = models.FloatField(db_index=True, null=True, blank=True)
  end_cdma_lat = models.FloatField(db_index=True, null=True, blank=True)
  rating_time = models.DateTimeField(blank=True, null=True)
  speed = models.FloatField(null=True, blank=True)
  altitude = models.FloatField(null=True, blank=True)
  is_dual = models.BooleanField(db_index=True, default=False)
  is_roaming = models.BooleanField(db_index=True, default=False)
  is_network_roaming = models.BooleanField(default=False)
  call_drop = models.IntegerField(db_index=True, null=True)
  rating = models.FloatField(db_index=True, null=True, blank=True)
  state_name = models.CharField(db_index=True, null=True, blank=True, max_length=255)
  additional_info = models.CharField(db_index=True, null=True, blank=True, max_length=216)
  comment = models.CharField(null=True, blank=True, max_length=512)
  in_out = models.IntegerField(db_index=True, null=True)
  created = models.DateTimeField(db_index=True, auto_now_add=True)
ankit
  • 1,499
  • 5
  • 29
  • 46
  • what does the `FeedbackData.objects.annotate(year=ExtractYear('created'), month=ExtractMonth('created')).values('year','month','start_operator_alias')` return? – Anton Shurashov Aug 15 '17 at 07:44
  • @AntonShurashov It Returns ... Basically I am trying to group data by Year, Month and then find the count – ankit Aug 17 '17 at 06:32
  • Yes, I understand and it's a strange behavior. Try to run query without `.annotate(dcount=Count('*'))` to understand it's problem in a `Count` function or in the `created` field. If the query without `Count` is also with empty years and months, check data in the `created` field, maybe it's empty or it has an incorrect type in the database. – Anton Shurashov Aug 17 '17 at 08:25
  • @AntonShurashov Below Query worked fine FeedbackData.objects.extra({"year":"EXTRACT(YEAR from created)", "month":"EXTRACT(MONTH from created)"}).values('year','month','start_operator_alias').annotate(dcount=Count('*')).values('year', 'month','start_operator_alias','dcount').order_by() – ankit Aug 17 '17 at 16:32

1 Answers1

3

This will happen if you do not have any time zone info loaded into mysql

convert_tz returns null

Jeffrey Wahl
  • 31
  • 2