1

I want to query to MySQL using Django.models, but since I put my created_at (Datetime field) with datetime.datetime.now(), it seems to hard to "group by" with each date. I made a query like this:

start = time.strptime("2012/6/25","%Y/%m/%d")
end = time.strptime("2012/6/26","%Y/%m/%d")
unix_start= time.mktime(start)
unix_end = time.mktime(end)

Mymodels.objects.filter(created_at__range[datetime.datetime.fromtimestamp(unix_start),datetime.datetime.fromtimestamp(unix_end]).values("~~~~~").annotate(player_id=Count("player_id"))

but I guess this query is too heavy for network.Is there any way to "group by" by datetime.date or use (date() for MySQL)in Django.models when my MySQL data is datetime.datetime.now()?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
suslov
  • 129
  • 6

1 Answers1

1

I use PostgreSQL date_trunc() to group values by a date when the dates are saved as Datetime. I think you can use something like this with MySQL:

select_date = {"date": """DATE_FORMAT(start, '%Y-%m-%d')"""}
query = query.extra(select=select_date).values('date')
query = query.annotate(player_id=Count('player_id', distinct=True)).order_by('date')
AJJ
  • 7,365
  • 7
  • 31
  • 34
  • thanks!I made it with this : cls.objects.extra(select={'month': 'extract( month from created_at )','day':'extract(day from created_at)'}).values("day","month","~~~","~~~").annotate(id=Count("id")) – suslov Jun 29 '12 at 13:37
  • you have to do "from django.db.models import Count" in advance. – suslov Jun 29 '12 at 13:39