0

I have a model with a time_stamp (DateTime field) and I want to get the latest entry of every month.

How can I approach this problem?

Model:

class Transaction (models.Model):
    transaction_id = models.AutoField(primary_key=True)
    net_monthly_transaction = models.DecimalField(max_digits = 10, decimal_places = 2, default=0)
    # deposit or withdrawal (withdrawal with negative value)
    amount = models.DecimalField(max_digits = 10, decimal_places = 2)
    time_stamp = models.DateTimeField(default=datetime.now, blank=True)


    def __str__(self):              # __unicode__ on Python 2
        return str(self.time_stamp)  + str(self.amount) + str(self.net_monthly_transaction)

This can easily be accomplished in mysql which I am familiar with, and there are lots of answers for them in SO, here's an example:

SELECT * FROM table 
WHERE created_on in 
(select DISTINCT max(created_on) from table 
GROUP BY YEAR(created_on), MONTH(created_on))

Get last record of each month in MySQL....?

How can I accomplish this in Django? Any help or direction would be appreciated.

Thanks in advance,

Community
  • 1
  • 1
almost a beginner
  • 1,622
  • 2
  • 20
  • 41

1 Answers1

1

You can try the below code.

from django.db import connection

truncate_date = connection.ops.date_trunc_sql('month', 'time_stamp')
report = Transaction.objects.extra({'month':truncate_date}).values('month').annotate(last_record=Max('time_stamp'))
sriramganesh
  • 1,460
  • 1
  • 11
  • 7
  • I think I wasn't clear with my question, your query works, it clearly defines the last entry date as "last_record", I rather want the "net_monthly_transaction" value of the last entry date in every month. I am going through the docs right now to see if I can figure it out, but will be looking out for your edit. Thanks in advance, – almost a beginner Oct 10 '16 at 11:00
  • I have almost figured it out. I used annotate to get the highest "net_monthly_transaction" in each month. The problem is, a member can withdrawal, so the highest is not going to be the latest. Any suggestions? – almost a beginner Oct 11 '16 at 00:21
  • You want to find out latest net_monthly_transaction per month. Correct? If yes, the above query gives latest entry per month. Now if you want only "net_monthly_transaction". You can do something like this Transaction.objects.extra({'month':truncate_date}).values('month').annotate(last_record=Max('time_stamp')).values_list('net_monthly_transaction', flat=True) If no, can you please explain what you exactly needs? – sriramganesh Oct 11 '16 at 04:17
  • So I have been experimenting with the net_monthly_transaction values and this is what I found out. If I add 1000 for october, and then add 500, the query will not grab the 500. If the second value of the month is smaller than the first, then it is excluded, if third value is smaller than the first value then it is excluded. If I add 20 smaller values than the first, then they are all excluded. Somehow the query has set some condition based on the value of net_monthly_transaction??? The query grabs every value that is greater than the first value of the month. – almost a beginner Oct 11 '16 at 10:27