menu

Questions & Answers

How to write Django QuerySet to get UNIX_TIMESTAMP of a DateTimeField?

This question is a follow-up to the one here.

Suppose I have the following Django model:

from django.db import models
from django.db.models import Sum

class myModel(models.Model):
    my_string = models.CharField(max_length=32,)
    my_date = models.DateTimeField()

    @staticmethod
    def get_stats():            
        logger.info(myModel.objects.values('my_string').annotate(
                count=Count('my_string'), 
                sum=Sum(F('my_date')+0)), #THIS NEEDS TO CHANGE. TO WHAT??
            )
        )

Instead of calculating the Sum of my_date, I would like to calculate the sum of the MySQL expression UNIX_TIMESTAMP('my_date'). How can I modify the Django QuerySet above to accomplish that?

Note: this question not a duplicate of this one because I am asking how to get this value from a QuerySet (IE: How do I get the Database to return this value). In that question, there is no Database. It is straight non-Django python. I'm using Django.

Answers(1) :

Look at func-expressions

from django.db.models import Func, F, Sum

queryset.annotate(sum=Sum(Func(F('my_date'), function='UNIX_TIMESTAMP')))
Comments:
2023-01-24 00:55:06
I know this question was tagged MySQL, but for anyone else looking for a Postgres solution to the same problem, there is a good solution here