0
I have a table in the database that the date fields are of type Biginteger, in which receives a date in Timestamp format.
How do I make one SELECT
in the table with the following conditions:
SELECT
COUNT(log.id) AS QTD
FROM
mdl_logstore_standard_log log
INNER JOIN mdl_user ON mdl_user.id = log.userid
AND suspended = 0
AND deleted = 0
WHERE
MONTH (
from_unixtime(log.timecreated)
) = MONTH (now())
AND YEAR (
from_unixtime(log.timecreated)
) = YEAR (now())
AND action = 'loggedin'
AND userid > 1
GROUP BY
MONTH (
from_unixtime(log.timecreated)
),
YEAR (
from_unixtime(log.timecreated)
)
I need it to be the month and year number. But I don’t know how to convert the date using the Django Database API. I have it below so far:
totalAccessMonth = Log.objects.filter(timecreated__month = now.month).all()
What I want is to convert the column in real time to a date type. The date field currently at the base is bigint
. I can’t change it in the bank.
I need this consultation above in the molds of Django.
For now I have this:
Log.objects.filter(action = 'loggedin', userid__gt = 1) \
.extra(select={'timecreated_year' : 'YEAR(FROM_UNIXTIME(timecreated))'}) \
.extra(select={'timecreated_month' : 'MONTH(FROM_UNIXTIME(timecreated))'}) \
.values_list('timecreated_month', flat = True) \
.aggregate(Count('id'))
In this example query of yours, would you like to add the year as well as condition? For example: timecreated__month = now.Month, timecreated__year = now.year.
– escapistabr
I would like to. The problem is that it does not work because of the type of field.
– Diego Souza
Using raw_sql, would that be something? cursor = Connection.cursor() cursor.execute('SELECT cast(EXTRACT(year FROM column) as bigint) as YEARS ' 'FROM despesa_tbl WHERE cd_usuario = %s ' 'GROUP BY EXTRACT(year FROM column) ' 'ORDER BY EXTRACT(year FROM column) DESC ', [request.user.id])
– escapistabr
But there’s no way to do it using ORM?
– Diego Souza
I don’t know a way; when I needed to do this I go to raw_sql. I will study about it and if I get any solution I come back and comment.
– escapistabr