FWIW, on Postgres DBs, I've done the following:
qs = FooModel.objects.filter(date__gte=start_date,
date__lt=end_date).extra(select={'datetrunc': "date_trunc('month',
date)"}).values('datetrunc').annotate(total=Sum("value"))
date_trunc in postgres also accepts "day" and "week" truncations.
-- dz
2010/11/4 Rogério Carrasqueira <rogerio.carrasque...@gmail.com>
> Hello Folks!
>
> I've got the solution, putting here for future searchs:
>
> sales =
> Sale.objects.extra(select={'month':'month(date_created)','year':'year(date_created)'}).values('year','month').annotate(total_month=Sum('total_value'),
> average_month=Avg('total_value'))
>
> This query works only using MySQL, to use with PGSQL you need to know to
> work with EXTRACT clauses.
>
> Cheers
>
>
> Rogério Carrasqueira
>
> ---
> e-mail: rogerio.carrasque...@gmail.com
> skype: rgcarrasqueira
> MSN: rcarrasque...@hotmail.com
> ICQ: 50525616
> Tel.: (11) 7805-0074
>
>
>
> Em 4 de novembro de 2010 10:34, Rogério Carrasqueira <
> rogerio.carrasque...@gmail.com> escreveu:
>
> Hi Sebastien!
>>
>> Thanks for you reply. I'm a newbie on Django and I must confess
>> unfortunately I don't know everything yet ;-). So I saw that you made a
>> snippet regarding about the use of Django Cube. So, where do I put this
>> snippet: at my views.py? Or should I do another class at my models.py?
>>
>> Thanks so much!
>>
>> Regards,
>>
>> Rogério Carrasqueira
>>
>> ---
>> e-mail: rogerio.carrasque...@gmail.com
>> skype: rgcarrasqueira
>> MSN: rcarrasque...@hotmail.com
>>
>> ICQ: 50525616
>> Tel.: (11) 7805-0074
>>
>>
>>
>> 2010/10/29 sebastien piquemal <seb...@gmail.com>
>>
>> Hi !
>>>
>>> You could also give django-cube a try :
>>> http://code.google.com/p/django-cube/
>>> Unlike Mikhail's app, the aggregates are not efficient (because no
>>> optimization is made, I am working on this), but this is more than
>>> enough if you have a reasonable amount of data (less than millions of
>>> rows !!!).
>>> Use the following code, and you should have what you need :
>>>
>>> from cube.models import Cube
>>>
>>> class SalesCube(Cube):
>>>
>>> month = Dimension('date_created__absmonth',
>>> queryset=Sale.objects.filter(date_created__range=(init_date,ends_date)))
>>>
>>> @staticmethod
>>> def aggregation(queryset):
>>> return queryset.count()
>>>
>>> The advantage is that if you want to add more dimensions (type of sale/
>>> place/month, etc ...), you can do it very easily.
>>> Hope that helps, and don't hesitate to ask me if you can't have it
>>> working (documentation is not very good yet).
>>>
>>> On Oct 29, 12:54 am, Mikhail Korobov <kmik...@googlemail.com> wrote:
>>> > Hi Rogério,
>>> >
>>> > You can givehttp://bitbucket.org/kmike/django-qsstats-magic/srca
>>> > try.
>>> > It currently have efficient aggregate lookups (1 query for the whole
>>> > time series) only for mysql but it'll be great if someone contribute
>>> > efficient lookups for other databases :)
>>> >
>>> > On 28 окт, 19:31, Rogério Carrasqueira
>>> >
>>> > <rogerio.carrasque...@gmail.com> wrote:
>>> > > Hello!
>>> >
>>> > > I'm having an issue to make complex queries in django. My problem is,
>>> I have
>>> > > a model where I have the sales and I need to make a report showing
>>> the sales
>>> > > amount per month, by the way I made this query:
>>> >
>>> > > init_date = datetime.date(datetime.now()-timedelta(days=365))
>>> > > ends_date = datetime.date(datetime.now())
>>> > > sales =
>>> > >
>>> Sale.objects.filter(date_created__range=(init_date,ends_date)).values(date_
>>> created__month).aggregate(total_sales=Sum('total_value'))
>>> >
>>> > > At the first line I get the today's date past one year
>>> > > after this I got the today date
>>> >
>>> > > at sales I'm trying to between a range get the sales amount grouped
>>> by
>>> > > month, but unfortunatelly I was unhappy on this, because this error
>>> > > appeared:
>>> >
>>> > > global name 'date_created__month' is not defined
>>> >
>>> > > At date_created is the field where I store the information about when
>>> the
>>> > > sale was done., the __moth was a tentative to group by this by month.
>>> >
>>> > > So, my question: how to do that thing without using a raw sql query
>>> and not
>>> > > touching on database independence?
>>> >
>>> > > Thanks so much!
>>> >
>>> > > Rogério Carrasqueira
>>> >
>>> > > ---
>>> > > e-mail: rogerio.carrasque...@gmail.com
>>> > > skype: rgcarrasqueira
>>> > > MSN: rcarrasque...@hotmail.com
>>> > > ICQ: 50525616
>>> > > Tel.: (11) 7805-0074
>>> >
>>> >
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "Django users" group.
>>> To post to this group, send email to django-us...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> django-users+unsubscr...@googlegroups.com<django-users%2bunsubscr...@googlegroups.com>
>>> .
>>> For more options, visit this group at
>>> http://groups.google.com/group/django-users?hl=en.
>>>
>>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-us...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com<django-users%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
