Reputation: 106102
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
}
]
Upvotes: 2
Views: 351
Reputation: 106102
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.
Upvotes: 1