Group by date in Django

Question

I am trying to achieve the result of the following SQL query

SELECT 
UNIX_TIMESTAMP(DATE((FROM_UNIXTIME(`timestamp`)))) AS `x`, 
COUNT(`timestamp`) as y 
    FROM somedb.events
        WHERE user_id=3 AND 
        `timestamp` > 1612117800 AND 
        `timestamp` < 1614450600 AND
        `kind`='food'
    GROUP BY `x`
    ORDER BY `x` desc;

using Django ORM. Expected output:

[
    {
        "x": 1613759400,
        "y": 2
    },
    {
        "x": 1612463400,
        "y": 1
    }
]

Here is what I tried so far:

queryset = events.objects.filter(
            user=request.user,
            timestamp__range=dates
        )

First approach:

result = queryset.annotate(
            trunc_date_timestamp=Func(
              Value(
                TruncDate(
                    Func(
                        F('timestamp'),
                        function='FROM_UNIXTIME',
                    )
                )
              ),
              function='UNIX_TIMESTAMP',
              output_field=models.IntegerField()
            )
        ).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

This produces the output:

[
    {
        "x": 0,
        "y": 3
    }
]

Second approach:

result = queryset.annotate(
            trunc_date_timestamp=Func(
                    Func(
                        F('timestamp'),
                        function='FROM_UNIXTIME',
                        output_field=models.DateField()
                    ),
                function='UNIX_TIMESTAMP'
            )
        ).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

produces the output:

[
    {
        "x": 1613831760,
        "y": 1
    },
    {
        "x": 1613810160,
        "y": 1
    },
    {
        "x": 1612520520,
        "y": 1
    }
]

Answer 1

Finally I made it working. I did tiny mistakes in both approach.

First approach:

result = ueryset.annotate(
            trunc_date_timestamp=Func(
                TruncDate(
                    Func(
                        F('timestamp'),
                        function='FROM_UNIXTIME',
                        output_field=models.DateField() # <<--- This is must
                    )
                ),
                function='UNIX_TIMESTAMP',
            )
        ).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

Removed Value() API and added output_field=models.DateTimeField() to inner Func() API. output_field will ensure that the field returned by inner Func() API must be of type models.DateField() on which TruncDate will be applied.

Second approach:

queryset = queryset.annotate(
            trunc_date_timestamp=Func(
                Cast(
                    Func(
                        F('timestamp'),
                        function='FROM_UNIXTIME',
                    ),
                    output_field=models.DateField()
                ),
                function='UNIX_TIMESTAMP'
            )
        ).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

Initially I thought

Func(
    F('timestamp'),
    function='FROM_UNIXTIME',
    output_field=models.DateField()
)

this will return a field of models.DateField() type but I don't know why it didn't and failed to work!!! So I used Cast() method instead and casted the returned expression to models.DateField() to make it work .

Though this solution work, I strongly feel that the original code for second approach should also work because if I understood Func() expression correctly then both of

Func(
   F('timestamp'),
   function='FROM_UNIXTIME',
   output_field=models.DateField()
) 

and

Cast(
    Func(
       F('timestamp'),
       function='FROM_UNIXTIME'
    ),
    output_field=models.DateField()
)

should produce the same result.